// ------------------------------------------------------------------------------------------
// Licensed by Interprise Solutions.
// http://www.InterpriseSolutions.com
// For details on this license please visit  the product homepage at the URL above.
// THE ABOVE NOTICE MUST REMAIN INTACT.
// ------------------------------------------------------------------------------------------
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Net;
using System.Net.Mail;
using System.Net.Mime;
using System.Text;
using System.Threading;
using System.Web;
using System.Xml.Linq;
using System.Xml.Serialization;
using DevExpress.XtraReports.UI;
using Interprise.Connectivity.Database.Configuration.Design.AppConfig;
using Interprise.Extendable.Base.Facade.Customer.CreditCardGateway;
using Interprise.Facade.Base;
using Interprise.Facade.Customer;
using Interprise.Facade.ECommerce;
using Interprise.Framework.Base.DatasetComponent;
using Interprise.Framework.Base.DatasetGateway;
using Interprise.Framework.Base.Shared;
using Interprise.Framework.Customer.DatasetGateway;
using Interprise.Framework.ECommerce.DatasetComponent;
using Interprise.Framework.ECommerce.DatasetGateway;
using Interprise.Licensing.Base.Services;
using InterpriseSuiteEcommerceCommon.DTO;
using InterpriseSuiteEcommerceCommon.Extensions;
using InterpriseSuiteEcommerceCommon.InterpriseIntegration;
using InterpriseSuiteEcommerceCommon.InterpriseIntegration.Web;
using Interprise.Framework.Customer.DatasetComponent;
using InterpriseSuiteEcommerceCommon.DataAccess;

namespace InterpriseSuiteEcommerceCommon
{
    public struct UnitMeasureInfo
    {
        public string Code;
        public decimal Quantity;

        public static readonly string ITEM_DEFAULT = string.Empty;

        public static UnitMeasureInfo ForItem(string itemCode, string unitMeasureCode)
        {
            UnitMeasureInfo info = new UnitMeasureInfo();

            bool useBaseUnitMeasure = string.IsNullOrEmpty(unitMeasureCode);
            string query = string.Empty;
            if (useBaseUnitMeasure)
            {
                query = string.Format("SELECT UnitMeasureCode, UnitMeasureQty FROM InventoryUnitMeasure with (NOLOCK) WHERE ItemCode = {0} AND  IsBase = 1", DB.SQuote(itemCode));
            }
            else
            {
                query = string.Format("SELECT UnitMeasureCode, UnitMeasureQty FROM InventoryUnitMeasure with (NOLOCK) WHERE ItemCode = {0} AND  UnitMeasureCode = {1}", DB.SQuote(itemCode), DB.SQuote(unitMeasureCode));
            }


            using (SqlConnection con = DB.NewSqlConnection())
            {
                con.Open();
                using (IDataReader reader = DB.GetRSFormat(con, query))
                {
                    if (reader.Read())
                    {
                        info.Code = DB.RSField(reader, "UnitMeasureCode");
                        info.Quantity = DB.RSFieldDecimal(reader, "UnitMeasureQty");
                    }
                }
            }

            return info;
        }
    }

    public class InterpriseHelper
    {
        #region " Variables "

        private static InterpriseConfiguration _configInstance;
        private static CryptoServiceProvider m_crypto = new CryptoServiceProvider();

        #endregion

        #region Constructor
        private InterpriseHelper() { }
        #endregion

        #region Property


        private static object _mutex = new object();
        #region ConfigInstance
        /// <summary>
        /// Gets the interprise configuration instance
        /// </summary>
        public static InterpriseConfiguration ConfigInstance
        {
            get
            {
                lock (_mutex)
                {
                    if (_configInstance == null)
                    {
                        
                        _configInstance = InterpriseConfiguration.Instance;

                        try
                        {
                            InterpriseConfiguration.Instance.CompanyInfo.CBNSkipLogin = true;
                            Interprise.Facade.Base.SimpleFacade.Instance.SignIn();
                        }
                        catch (Exception ex)
                        {
                            // NULL out this member so we will force checking next time around
                            _configInstance = null;
                            throw ex;
                        }
                    }
                }

                return _configInstance;
            }
        }


        #endregion

        public static byte[] GenerateSalt()
        {
            return m_crypto.GenerateSalt();
        }

        public static byte[] GenerateVector()
        {
            return m_crypto.GenerateVector();
        }

        #endregion

        #region Method

        #region Encryption

        public static String Encryption(String m_value)
        {
            String encryted_value = String.Empty;
            encryted_value = m_crypto.Encrypt(m_value, GenerateSalt(), GenerateVector());
            return encryted_value;
        }

        public static String Encryption(String value, byte[] salt, byte[] vector)
        {
            String encrypted_value = value;
            encrypted_value = m_crypto.Encrypt(value, salt, vector);
            return encrypted_value;
        }

        #endregion

        #region Decryption

        public static String Decryption(String m_value)
        {
            throw new InvalidOperationException("!!!");
        }

        public static String Decryption(byte[] value, byte[] salt, byte[] vector)
        {
            String decrypted_value = String.Empty;
            if (value.Length > 0 || salt.Length > 0 || vector.Length > 0)
            {
                decrypted_value = m_crypto.Decrypt(value, salt, vector);
            }
            return decrypted_value;
        }

        #endregion

        #region CRM Contact

        #region CRM Vector AND Salt
        public static ArrayList LoginSetting(String m_email)
        {
            ArrayList fArrayList = new ArrayList();
            bool validEmail = false;
            String sql = "SELECT PasswordSalt, PasswordIV FROM CrmContact with (NOLOCK) " +
                         "WHERE UserName=" + DB.SQuote(m_email);


            using (SqlConnection con = DB.NewSqlConnection())
            {
                con.Open();
                using (IDataReader rs = DB.GetRSFormat(con, sql))
                {//Used for logging in when there are more than 1 same email.
                    while (rs.Read())
                    {
                        fArrayList.Add(DB.RSField(rs, "PasswordSalt"));
                        fArrayList.Add(DB.RSField(rs, "PasswordIV"));
                        validEmail = true;
                    }
                    //goes here if invalid email address.
                    if (validEmail == false)
                    {
                        fArrayList = null;
                    }
                }
            }

            return fArrayList;
        }
        #endregion

        #region Validate CRM Contact password
        /// <summary>
        /// Validate password for multiple email address.
        /// </summary>
        /// <param name="Email">email address</param>
        /// <param name="Pass">password typed by user</param>
        /// <returns>true if password is valid for email.</returns>
        public static bool ValidatePassword(String Email, String Pass, ref String CustomerID)
        {
            ArrayList Password = new ArrayList(), PasswordSALT = new ArrayList(), PasswordVECTOR = new ArrayList(), NewCustomerID = new ArrayList();
            byte[] salt, vector;
            String m_cust = string.Empty;

            using (SqlConnection con = DB.NewSqlConnection())
            {
                con.Open();
                using (IDataReader rs = DB.GetRSFormat(con, String.Format("SELECT EntityCode, password, passwordsalt, passwordiv FROM CrmContact with (NOLOCK) WHERE Type = 'CustomerContact' AND UserName={0}", DB.SQuote(Email))))
                {
                    while (rs.Read())
                    {
                        Password.Add(DB.RSField(rs, "password"));
                        PasswordSALT.Add(DB.RSField(rs, "passwordsalt"));
                        PasswordVECTOR.Add(DB.RSField(rs, "passwordiv"));
                        NewCustomerID.Add(DB.RSField(rs, "EntityCode"));
                    }
                }
            }

            for (int i = 0; i < Password.Count; i++)
            {
                salt = Convert.FromBase64String(PasswordSALT[i].ToString()); vector = Convert.FromBase64String(PasswordVECTOR[i].ToString());
                if (Password[i].ToString() == Encryption(Pass, salt, vector))
                {
                    CustomerID = NewCustomerID[i].ToString();
                    return true;
                }
            }
            return false;
        }
        #endregion

        #region GetUserPreferredLanguageCode
        /// <summary>
        /// Gets the preferred language code of the user
        /// </summary>
        /// <param name="userCode">The user code</param>
        /// <returns></returns>
        public static string GetUserPreferredLanguageCode(string userCode)
        {
            string languageCode = string.Empty;
            bool userFound = false;


            using (SqlConnection con = DB.NewSqlConnection())
            {
                con.Open();
                using (IDataReader reader = DB.GetRSFormat(con, "SELECT LanguageCode FROM SystemUserAccount with (NOLOCK) WHERE UserCode = {0}", DB.SQuote(userCode)))
                {
                    userFound = reader.Read();
                    if (userFound)
                    {
                        languageCode = DB.RSField(reader, "LanguageCode");
                    }
                }
            }

            if (!userFound) throw new ArgumentException("User not found!", userCode);

            return languageCode;
        }
        #endregion

        #endregion

        #region TopicPassword
        public static string TopicPassword(string TopicID, string LocaleSetting)
        {
            string password = string.Empty;

            using (SqlConnection con = DB.NewSqlConnection())
            {
                con.Open();
                using (IDataReader dr = DB.GetRSFormat(con, string.Format("SELECT * FROM EcommerceWebTopicView with (NOLOCK) WHERE TopicID={0} AND LocaleSetting={1} AND WebSiteCode={2}",
                                                                            DB.SQuote(TopicID), DB.SQuote(LocaleSetting), DB.SQuote(ConfigInstance.WebSiteCode))))
                {
                    if (dr.Read())
                    {
                        password = InterpriseHelper.Decryption(Convert.FromBase64String(DB.RSField(dr, "Password")), Convert.FromBase64String(DB.RSField(dr, "PasswordSalt")),
                            Convert.FromBase64String(DB.RSField(dr, "PasswordIV")));
                    }
                }
            }

            return password;
        }
        #endregion

        #region Validation

        #region ValidateClientsIP
        public static Int64 ValidateClientIP(String IPAdd)
        {
            Int64 ipNo = 0;
            System.Net.IPAddress mip = System.Net.IPAddress.Parse(IPAdd);

            foreach (byte b in mip.GetAddressBytes())
            {
                ipNo = ipNo * 256 + b;
            }
            return (ipNo);
        }
        #endregion

        #region SelectedCountry
        private static String SelectedCountry(String Country)
        {
            String mCountry = String.Empty;
            if (Country == GetCountryCode())
            {
                mCountry = String.Format("selected=\"selected\" value={0}>", SDQuote(Country));
            }
            else
            {
                mCountry = String.Format("value={0}>", SDQuote(Country));
            }
            return mCountry;
        }
        #endregion

        #region ValidateEntityID
        public static string ValidateEntityID(String EntityName, String EntityCode)
        {
            String m_val = String.Empty;

            return m_val;
        }
        #endregion

        #region ValidateResidenceType

        public static int ValidateResidenceType(string ResidenceID)
        {
            int m_id = 0;
            switch (ResidenceID.ToLowerInvariant())
            {
                case "unknown":
                    m_id = 0;
                    break;
                case "residential":
                    m_id = 1;
                    break;
                case "commercial":
                    m_id = 2;
                    break;
                default:
                    break;
            }
            return m_id;

        }

        public static ResidenceTypes ResolveResidenceType(string residenceType)
        {
            ResidenceTypes type = ResidenceTypes.Unknown;

            // check if it's an integer...
            int intResidenceType;
            if (int.TryParse(residenceType, out intResidenceType))
            {
                // it's an anon customer
                switch (intResidenceType)
                {
                    case (int)ResidenceTypes.Unknown:
                        type = ResidenceTypes.Unknown;
                        break;
                    case (int)ResidenceTypes.Commercial:
                        type = ResidenceTypes.Commercial;
                        break;
                    case (int)ResidenceTypes.Residential:
                        type = ResidenceTypes.Residential;
                        break;
                }
            }
            else
            {
                switch (residenceType.ToLowerInvariant())
                {
                    case "unknown":
                        type = ResidenceTypes.Unknown;
                        break;
                    case "residential":
                        type = ResidenceTypes.Residential;
                        break;
                    case "commercial":
                        type = ResidenceTypes.Commercial;
                        break;
                    default:
                        break;
                }
            }

            return type;
        }

        #endregion

        #region ValidateCountryCode
        public static string ValidateCountryCode(string m_countrycode, string m_statecode)
        {
            string countrycode = string.Empty;
            if (m_countrycode == "United States of America")
            {
                countrycode = m_statecode;
            }
            return countrycode;
        }
        #endregion

        #endregion

        #region Country

        #region GetCountryCode

        public static string GetClientCountryCode()
        {
            return GetCountryCode();
        }
        public static String GetCountryCode()
        {
            String CountryCode = String.Empty;

            using (SqlConnection con = DB.NewSqlConnection())
            {
                con.Open();
                using (IDataReader rs = DB.GetRSFormat(con, "SELECT * FROM WebIPCountry with (NOLOCK) WHERE " + ValidateClientIP(CommonLogic.ServerVariables("REMOTE_ADDR")) + " BETWEEN IpFrom AND IpTo"))
                {
                    if (rs.Read())
                    {
                        CountryCode = DB.RSField(rs, "CountryLong");
                        if (CountryCode.Contains("-"))
                        {
                            CountryCode = InterpriseHelper.ConfigInstance.UserInfo.UserCountry;
                        }
                    }
                }
            }

            return CountryCode;
        }

        #endregion

        #region IsCountryCodeSearchable
        private static bool IsCountryCodeSearchable()
        {
            bool IsSearchable = false;


            using (SqlConnection con = DB.NewSqlConnection())
            {
                con.Open();
                using (IDataReader rs = DB.GetRSFormat(con, "SELECT * FROM SystemCountry with (NOLOCK) WHERE CountryCode=" +
                                                                DB.SQuote(GetCountryCode()) + " AND IsSearchablePostal = 1"))
                {
                    if (rs.Read())
                    {
                        IsSearchable = true;
                    }
                    else
                    {
                        IsSearchable = false;
                    }
                }
            }

            return IsSearchable;
        }
        #endregion

        #region CountryList

        public static String CountryList(String name)
        {
            StringBuilder CountryList = new StringBuilder();

            CountryList.Append("<select name=" + SDQuote(name) + "id=" + SDQuote(name) + ">");


            using (SqlConnection con = DB.NewSqlConnection())
            {
                con.Open();
                using (IDataReader rs = DB.GetRSFormat(con, "SELECT * FROM SystemCountry with (NOLOCK) ORDER BY CountryCode"))
                {
                    while (rs.Read())
                    {
                        CountryList.Append("<option " +
                            InterpriseSuiteEcommerceCommon.InterpriseHelper.SelectedCountry(DB.RSField(rs, "CountryCode"))
                            + DB.RSField(rs, "CountryCode") + "</option>");
                    }
                }
            }

            CountryList.Append("</select>");

            return CountryList.ToString();
        }
        #endregion

        #endregion

        #region Manipulation

        #region SDQuote
        public static String SDQuote(String s)
        {
            return "\"" + s + "\"";
        }
        #endregion

        #endregion

        #region AddCustomerShipTo

        public static void AddCustomerBillToInfo(string customerCode, bool makeDefault)
        {
            AddCustomerBillToInfo(customerCode, null, makeDefault);
        }

        private static string[] CultureSafeExpMonths()
        {
            List<string> expMonths = new List<string>();

            CultureInfo culture = new CultureInfo(Localization.CompanyLocale());

            DateTime current = DateTime.Today;
            int year = current.Year;

            for (int month = 1; month <= 12; month++)
            {
                DateTime firstDateOfMonth = new DateTime(year, month, 1);
                expMonths.Add(firstDateOfMonth.ToString("MMM", culture));
            }

            return expMonths.ToArray();
        }

        public static string FromInterpriseExpMonth(string expMonth)
        {
            string[] expMonths = CultureSafeExpMonths();

            int index = -1;
            for (int ctr = 0; ctr < expMonths.Length; ctr++)
            {
                if (expMonth.Equals(expMonths[ctr], StringComparison.InvariantCultureIgnoreCase))
                {
                    index = (ctr + 1);
                    break;
                }
            }

            if (index < 0) { index = 1; }

            return CommonLogic.IIF(index < 10, index.ToString().PadLeft(2, '0'), index.ToString());
        }

        public static string ToInterpriseExpMonth(string expMonth)
        {
            string[] expMonths = CultureSafeExpMonths();

            int index = 0;
            if (int.TryParse(expMonth, out index))
            {
                index -= 1;
                if (index < expMonths.Length)
                {
                    return expMonths[index];
                }
            }
            else
            {
                return expMonths[0];
            }
            return null;
        }

        public static void AddCustomerBillToInfo(string customerCode, Address billToAddress, bool makeDefault, string email = "")
        {
            using (var gatewayCreditCardDataset = new CreditCardDatasetGateway())
            {
                using (var facadeCreditCard = new CreditCardFacade(gatewayCreditCardDataset))
                {
                    facadeCreditCard.AddCreditCard();
                    var creditCardRow = gatewayCreditCardDataset.CustomerCreditCardView[0];

                    if (billToAddress != null)
                    {
                        creditCardRow.BeginEdit();
                        creditCardRow.CustomerName = billToAddress.Name;
                        creditCardRow.CustomerCode = customerCode;
                        creditCardRow.NameOnCard = billToAddress.Name;
                        creditCardRow.CreditCardDescription = "Web Credit Card";
                        creditCardRow.ResidenceType = billToAddress.ResidenceType.ToString();
                        creditCardRow.Address = billToAddress.Address1;
                        creditCardRow.City = billToAddress.City;
                        creditCardRow.State = billToAddress.State;
                        creditCardRow.Country = billToAddress.Country;
                        creditCardRow.County = billToAddress.County;
                        creditCardRow.Telephone = billToAddress.Phone;
                        creditCardRow.Email = billToAddress.EMail;

                        #region Postal Code Handler

                        string postal = String.Empty;

                        if (billToAddress.Country == DomainConstants.COUNTRY_US)
                        {
                            int digits = InterpriseHelper.GetPostalCodeDigits(billToAddress.PostalCode, false);
                            postal = CommonLogic.IIF(digits == 0, billToAddress.PostalCode, digits.ToString("00000.##"));

                            int postalPlus4 = InterpriseHelper.GetPostalCodeDigits(billToAddress.PostalCode, true);
                            if (postalPlus4 > 0)
                            {
                                creditCardRow.Plus4 = postalPlus4;
                            }
                        }
                        else
                        {
                            postal = billToAddress.PostalCode;
                        }

                        creditCardRow.PostalCode = postal;

                        #endregion

                        var companyCulture = new CultureInfo(Localization.CompanyLocale());
                        creditCardRow.ExpMonth = DateTime.Now.ToString("MMM", companyCulture);
                        creditCardRow.StartMonth = DateTime.Now.ToString("MMM", companyCulture);

                        creditCardRow.EndEdit();
                    }
                    else
                    {
                        // adding
                        DataSet customerDataset = new DataSet();
                        var facadeListControl = new ListControlFacade();

                        BaseDataset customerBaseDataset = new BaseDataset();
                        customerDataset = facadeListControl.ReadSearchResultsData("CustomerDropdownListView", string.Format("EntityCode = {0}", DB.SQuote(customerCode)), 1, false, string.Empty, ref customerBaseDataset, true);

                        if (customerDataset.Tables["CustomerDropdownListView"] != null &&
                            customerDataset.Tables["CustomerDropdownListView"].Rows.Count > 0)
                        {
                            DataRow customerRow = customerDataset.Tables["CustomerDropdownListView"].Rows[0];
                            facadeCreditCard.AssignCustomer(customerRow);

                            creditCardRow.CreditCardDescription = "Web Credit Card";

                            CultureInfo companyCulture = new CultureInfo(Localization.CompanyLocale());
                            creditCardRow.ExpMonth = DateTime.Now.ToString("MMM", companyCulture);
                            creditCardRow.StartMonth = DateTime.Now.ToString("MMM", companyCulture);
                        }

                        //**************************************************
                        // Explicit Disposal and De-Referencing goes here
                        //**************************************************
                        customerDataset.Dispose();
                        customerBaseDataset.Dispose();
                        facadeListControl.Dispose();

                        customerDataset = null;
                        customerBaseDataset = null;
                        facadeListControl = null;
                    }

                    if (!facadeCreditCard.Validate() && gatewayCreditCardDataset.HasErrors)
                    {
                        StringBuilder error = new StringBuilder();

                        foreach (DataColumn errCol in creditCardRow.GetColumnsInError())
                        {
                            error.Append(creditCardRow.GetColumnError(errCol));
                            error.AppendLine();
                        }

                        throw new InvalidOperationException(error.ToString());
                    }

                    string[][] commands = new string[][]{new string[]{
                                            CreditCardDatasetGateway.CUSTOMERCREDITCARDVIEW_TABLE,
                                            StoredProcedures.CREATECUSTOMERCREDITCARD,
                                            StoredProcedures.UPDATECUSTOMERCREDITCARD,
                                            StoredProcedures.DELETECUSTOMERCREDITCARD}};

                    facadeCreditCard.UpdateDataSet(commands, Interprise.Framework.Base.Shared.Enum.TransactionType.CustomerCreditCard, string.Empty, false);

                    if (makeDefault)
                    {
                        //if this is going to be the default billing address
                        //make sure we have Telephone on Customer table since this is required.
                        //if Customer was created in IS, this field is not required!
                        string customerTelephone = string.Empty;


                        using (SqlConnection con = DB.NewSqlConnection())
                        {
                            con.Open();
                            using (IDataReader rs = DB.GetRSFormat(con, string.Format("SELECT Telephone FROM Customer with (NOLOCK) WHERE CustomerCode={0}", DB.SQuote(customerCode))))
                            {
                                if (rs.Read())
                                {
                                    customerTelephone = DB.RSField(rs, "Telephone");
                                }
                            }
                        }

                        if (CommonLogic.IsStringNullOrEmpty(customerTelephone))
                        {
                            customerTelephone = billToAddress.Phone;
                        }

                        DB.ExecuteSQL(
                            "UPDATE Customer SET CreditCardCode = {0}, Telephone = {1} WHERE CustomerCode = {2}",
                            DB.SQuote(gatewayCreditCardDataset.CustomerCreditCardView[0].CreditCardCode),
                            DB.SQuote(customerTelephone),
                            DB.SQuote(customerCode)
                        );

                        var currentCustomer = Customer.Current;
                        if (currentCustomer != null)
                        {
                            email = currentCustomer.EMail;
                        }

                        DB.ExecuteSQL(
                            "UPDATE CRMContact SET BusinessPhone = {0}, DefaultBillingCode = {1} WHERE EntityCode = {2} AND Email1 = {3}",
                            DB.SQuote(customerTelephone),
                            DB.SQuote(gatewayCreditCardDataset.CustomerCreditCardView[0].CreditCardCode),
                            DB.SQuote(customerCode),
                            DB.SQuote(email)
                        );
                    }
                }
            }
        }

        public static void MakeDefaultAddress(string contactCode, string defaultAddressID, AddressTypes addressType)
        {
            switch (addressType)
            {
                case AddressTypes.Billing:
                    DB.ExecuteSQL(
                        "UPDATE CRMContact SET DefaultBillingCode = {0} WHERE ContactCode = {1}",
                        DB.SQuote(defaultAddressID),
                        DB.SQuote(contactCode)
                    );
                    break;
                case AddressTypes.Shipping:
                    DB.ExecuteSQL(
                        "UPDATE CRMContact SET DefaultShippingCode = {0} WHERE ContactCode = {1}",
                        DB.SQuote(defaultAddressID),
                        DB.SQuote(contactCode)
                    );


                    if (!AppLogic.AppConfigBool("AllowMultipleShippingAddressPerOrder"))
                    {
                        DB.ExecuteSQL(String.Format("UPDATE EcommerceShoppingCart SET ShippingAddressID={0} WHERE ContactCode={1}",
                            DB.SQuote(defaultAddressID),
                            DB.SQuote(contactCode))
                        );
                    }
                    break;
            }
        }

        private static void DeActivateAddress(string customerCode, string addressid, AddressTypes addressType)
        {
            switch (addressType)
            {
                case AddressTypes.Billing:
                    break;
                case AddressTypes.Shipping:
                    break;
            }
        }

        public static void UpdateCustomerBillToInfo(Customer thisCustomer, Address billToAddress)
        {
            if (billToAddress.AddressType != AddressTypes.Billing) return;

            if (thisCustomer.IsRegistered)
            {
                using (var gatewayCreditCardDataset = new CreditCardDatasetGateway())
                {
                    using (var facadeCreditCard = new CreditCardFacade(gatewayCreditCardDataset))
                    {
                        // NOTE:
                        //  For some reason, the compiler doesn't seem to recognize
                        //  this overload from CreditCardFacade, it may be caused by the
                        //  last parameter not being CLS Compliant. So we'll downreference
                        //  our needed overloaded method to it's base.
                        //Interprise.Facade.Base.BaseFacade facadeBase = facadeCreditCard;

                        facadeCreditCard.LoadDataSet(new string[][]{ new string[]{
                                CreditCardDatasetGateway.CUSTOMERCREDITCARDVIEW_TABLE,
                                StoredProcedures.READCUSTOMERCREDITCARD,
                                "@CreditCardCode", 
                                billToAddress.AddressID}},
                            Interprise.Framework.Base.Shared.Enum.ClearType.Specific,
                            Interprise.Framework.Base.Shared.Enum.ConnectionStringType.Online);

                        // let's make sure we won't be using this anymore
                        //facadeBase = null;

                        var creditCardRow = gatewayCreditCardDataset.CustomerCreditCardView[0];

                        creditCardRow.BeginEdit();

                        bool hasChanges = CustomerDA.HasChangesToAddressInfo(creditCardRow, billToAddress);

                        creditCardRow.CustomerName = billToAddress.CardName;
                        creditCardRow.CustomerCode = billToAddress.CustomerCode;
                        creditCardRow.NameOnCard = billToAddress.CardName;
                        creditCardRow.Address = billToAddress.Address1;
                        creditCardRow.City = billToAddress.City;
                        creditCardRow.State = billToAddress.State;
                        creditCardRow.County = billToAddress.County;
                        creditCardRow.Country = billToAddress.Country;
                        creditCardRow.Telephone = billToAddress.Phone;
                        creditCardRow.Email = billToAddress.EMail;
                        creditCardRow.ResidenceType = billToAddress.ResidenceType.ToString();

                        #region Postal Code Handler

                        string postal = String.Empty;

                        if (billToAddress.Country == DomainConstants.COUNTRY_US)
                        {
                            int digits = InterpriseHelper.GetPostalCodeDigits(billToAddress.PostalCode, false);
                            postal = CommonLogic.IIF(digits == 0, billToAddress.PostalCode, digits.ToString("00000.##"));

                            int postalPlus4 = InterpriseHelper.GetPostalCodeDigits(billToAddress.PostalCode, true);
                            if (postalPlus4 > 0)
                            {
                                creditCardRow.Plus4 = postalPlus4;
                            }
                            else
                            {
                                CustomerDA.ClearCustomerAddressPlus4Field(billToAddress.AddressID, AddressTypes.Billing);
                            }

                        }
                        else
                        {
                            postal = billToAddress.PostalCode;
                            CustomerDA.ClearCustomerAddressPlus4Field(billToAddress.AddressID, AddressTypes.Shipping);
                        }

                        creditCardRow.PostalCode = postal;

                        #endregion


                        if (!billToAddress.CardNumber.IsNullOrEmptyTrimmed())
                        {
                            creditCardRow.MaskedCardNumber = (billToAddress.CardNumber.StartsWith("X")) ? billToAddress.CardNumber : Interprise.Framework.Base.Shared.Common.MaskCardNumber(billToAddress.CardNumber);
                            creditCardRow.ExpMonth = InterpriseHelper.ToInterpriseExpMonth(billToAddress.CardExpirationMonth);
                            creditCardRow.ExpYear = billToAddress.CardExpirationYear;
                            creditCardRow.StartMonth = (billToAddress.CardStartMonth != null) ? InterpriseHelper.ToInterpriseExpMonth(billToAddress.CardStartMonth) : null;
                            creditCardRow.StartYear = billToAddress.CardStartYear;
                            creditCardRow.CreditCardType = billToAddress.CardType;
                            creditCardRow.CreditCardDescription = (billToAddress.CardDescription == string.Empty) ? "Web Credit Card" : billToAddress.CardDescription;
                        }
                        else
                        {
                            if (hasChanges)
                            {
                                creditCardRow.CardNumber = string.Empty;
                                creditCardRow.MaskedCardNumber = string.Empty;
                                creditCardRow.ExpMonth = string.Empty;
                                creditCardRow.ExpYear = string.Empty;
                                creditCardRow.CreditCardType = string.Empty;
                                creditCardRow.CreditCardSalt = string.Empty;
                                creditCardRow.CreditCardIV = string.Empty;
                                creditCardRow.CreditCardDescription = string.Empty;
                                creditCardRow.InterpriseGatewayRefNo = 0;
                            }
                        }

                        creditCardRow.EndEdit();

                        string[][] updateCommandset = new string[][]{ new string[]{
                                                            CreditCardDatasetGateway.CUSTOMERCREDITCARDVIEW_TABLE,
                                                            StoredProcedures.CREATECUSTOMERCREDITCARD,
                                                            StoredProcedures.UPDATECUSTOMERCREDITCARD,
                                                            StoredProcedures.DELETECUSTOMERCREDITCARD }};

                        facadeCreditCard.UpdateDataSet(updateCommandset, Interprise.Framework.Base.Shared.Enum.TransactionType.CustomerCreditCard, string.Empty, false);

                        if (!billToAddress.CardType.IsNullOrEmptyTrimmed())
                        {
                            CustomerDA.UpdateCustomerCreditCardType(gatewayCreditCardDataset.CustomerCreditCardView[0].CustomerCode,
                                                                gatewayCreditCardDataset.CustomerCreditCardView[0].CreditCardCode,
                                                                billToAddress.CardType);
                        }

                        if (thisCustomer.PrimaryBillingAddressID == billToAddress.AddressID)
                        {
                            CustomerDA.UpdateCustomerBusinessType(gatewayCreditCardDataset.CustomerCreditCardView[0].CustomerCode, thisCustomer.BusinessType);

                            CustomerDA.UpdateCustomerBusinessTaxNumber(gatewayCreditCardDataset.CustomerCreditCardView[0].CustomerCode, thisCustomer.TaxNumber);
                        }
                    }
                }
            }
            else
            {
                billToAddress.Save();
            }
        }

        public static void UpdateCustomerShipToInfo(Customer thisCustomer, Address shipToAddress)
        {
            if (shipToAddress.AddressType != AddressTypes.Shipping) return;
            
            if (thisCustomer.IsRegistered)
            {
                using (var gatewayShipTo = new ShipToDatasetGateway())
                {
                    using (var facadeShipTo = new ShipToFacade(gatewayShipTo))
                    {

                        facadeShipTo.LoadDataSet(
                            new string[][]{new string[]{
                            CustomerDetailDatasetGateway.CUSTOMERSHIPTOVIEW_TABLE,
                            StoredProcedures.READCUSTOMERSHIPTO}},
                                new string[][] { new string[] { "@CustomerCode", shipToAddress.CustomerCode }, 
                                new string[] { "@ShipToCode", shipToAddress.AddressID }},
                            Interprise.Framework.Base.Shared.Enum.ClearType.Specific,
                            Interprise.Framework.Base.Shared.Enum.ConnectionStringType.Online
                        );

                        var shipToRow = gatewayShipTo.CustomerShipToView[0];

                        shipToRow.BeginEdit();
                        shipToRow.ShipToName = shipToAddress.Name;
                        shipToRow.Address = shipToAddress.Address1;
                        shipToRow.City = shipToAddress.City;
                        shipToRow.State = shipToAddress.State;
                        shipToRow.County = shipToAddress.County;
                        shipToRow.Country = shipToAddress.Country;
                        shipToRow.Telephone = shipToAddress.Phone;
                        shipToRow.AddressType = shipToAddress.ResidenceType.ToString();

                        #region Postal Code Handler

                        string postal = String.Empty;

                        if (shipToAddress.Country == DomainConstants.COUNTRY_US)
                        {
                            int digits = InterpriseHelper.GetPostalCodeDigits(shipToAddress.PostalCode, false);
                            postal = CommonLogic.IIF(digits == 0, shipToAddress.PostalCode, digits.ToString("00000.##"));

                            int postalPlus4 = InterpriseHelper.GetPostalCodeDigits(shipToAddress.PostalCode, true);
                            if (postalPlus4 > 0)
                            {
                                shipToRow.Plus4 = postalPlus4;
                            }
                            else
                            {
                                CustomerDA.ClearCustomerAddressPlus4Field(shipToRow.ShipToCode, AddressTypes.Shipping);
                            }

                        }
                        else
                        {
                            postal = shipToAddress.PostalCode;
                            CustomerDA.ClearCustomerAddressPlus4Field(shipToRow.ShipToCode, AddressTypes.Shipping);
                        }

                        shipToRow.PostalCode = postal;

                        #endregion

                        shipToRow.EndEdit();

                        string[][] updateCommandset =
                            new string[][]{
                            new string[]{ 
                            CustomerDetailDatasetGateway.CUSTOMERSHIPTOVIEW_TABLE,
                            StoredProcedures.CREATECUSTOMERSHIPTO,
                            StoredProcedures.UPDATECUSTOMERSHIPTO,
                            StoredProcedures.DELETECUSTOMERSHIPTO},
                        };

                        facadeShipTo.UpdateDataSet(updateCommandset, Interprise.Framework.Base.Shared.Enum.TransactionType.CustomerShipTo, string.Empty, false);
                    }
                }
            }
        }

        public static string AddCustomerShipTo(Address customerShipToAddress, Customer customer = null)
        {
            try
            {
                string shipToCode = string.Empty;

                using (var customerGateway = new Interprise.Framework.Base.DatasetGateway.Customer.NewCustomerDetailDatasetGateway())
                {
                    using (var customerFacade = new Interprise.Facade.Base.Customer.NewCustomerDetailFacade(customerGateway))
                    {
                        // NOTE :
                        //  For the new overload, since the customer tables are being discarded
                        //  placing dummy values here for the 3rd and 4th parameters shouldn't cause us trouble
                        InterpriseSuiteEcommercePrincipal principal = null;
                        if (HttpContext.Current != null)
                        {
                            principal = HttpContext.Current.User as InterpriseSuiteEcommercePrincipal;
                        }

                        Customer anonCustomer = null;
                        if (null != principal)
                        {
                            anonCustomer = principal.ThisCustomer;
                        }
                        else if (customer != null)
                        {
                            anonCustomer = customer;
                        }
                        else
                        {
                            anonCustomer = Customer.MakeAnonymous();
                        }

                        customerFacade.AddCustomer(
                            customerShipToAddress.CustomerCode,
                            customerShipToAddress.Name,
                            false,
                            anonCustomer.BusinessType.ToString());

                        customerGateway.CustomerShipToView[0].BeginEdit();
                        customerGateway.CustomerShipToView[0].CustomerName = customerShipToAddress.Name;
                        customerGateway.CustomerShipToView[0].ShipToName = customerShipToAddress.Name;
                        customerGateway.CustomerShipToView[0].Address = customerShipToAddress.Address1;
                        customerGateway.CustomerShipToView[0].City = customerShipToAddress.City;
                        customerGateway.CustomerShipToView[0].State = customerShipToAddress.State;
                        customerGateway.CustomerShipToView[0].Country = customerShipToAddress.Country;
                        customerGateway.CustomerShipToView[0].County = customerShipToAddress.County;
                        customerGateway.CustomerShipToView[0].Telephone = customerShipToAddress.Phone;
                        customerGateway.CustomerShipToView[0].CurrencyCode = customerShipToAddress.ThisCustomer.CurrencyCode;
                        customerGateway.CustomerShipToView[0].AddressType = customerShipToAddress.ResidenceType.ToString();

                        #region Postal Code Handler

                        string postal = String.Empty;

                        if (customerShipToAddress.Country == DomainConstants.COUNTRY_US)
                        {
                            int digits = InterpriseHelper.GetPostalCodeDigits(customerShipToAddress.PostalCode, false);
                            postal = CommonLogic.IIF(digits == 0, customerShipToAddress.PostalCode, digits.ToString("00000.##"));

                            int postalPlus4 = InterpriseHelper.GetPostalCodeDigits(customerShipToAddress.PostalCode, true);
                            if (postalPlus4 > 0)
                            {
                                customerGateway.CustomerShipToView[0].Plus4 = postalPlus4;
                            }
                        }
                        else
                        {
                            postal = customerShipToAddress.PostalCode;
                        }

                        customerGateway.CustomerShipToView[0].PostalCode = postal;

                        #endregion

                        customerGateway.CustomerShipToView[0].EndEdit();

                        customerFacade.CopyCustomerShipTo(customerGateway.Tables["CustomerShipToView"].Rows[0], customerShipToAddress.CustomerCode);
                        customerFacade.AddressType = Interprise.Framework.Base.Shared.Enum.NewCustomerAddressType.ShippingAddressOnly;
                        customerFacade.AssignDefaultClassTemplate(anonCustomer.BusinessType.ToString(), false);

                        string[][] commandSet = new string[][] {
                                                    new string[] {customerGateway.CustomerShipToView.TableName, 
                                                        StoredProcedures.CREATECUSTOMERSHIPTO, 
                                                        StoredProcedures.UPDATECUSTOMERSHIPTO, 
                                                        StoredProcedures.DELETECUSTOMER}, 
                                                    new string[] {customerGateway.CustomerAccount.TableName, 
                                                        StoredProcedures.CREATECUSTOMERACCOUNT, 
                                                        StoredProcedures.UPDATECUSTOMERACCOUNT, 
                                                        StoredProcedures.DELETECUSTOMERACCOUNT}};

                        customerGateway.CustomerAccount.RejectChanges();
                        customerGateway.CustomerView.RejectChanges();

                        customerFacade.UpdateDataSet(commandSet, Interprise.Framework.Base.Shared.Enum.TransactionType.CustomerShipTo, string.Empty, false);

                        customerShipToAddress.AddressID = customerGateway.CustomerShipToView[0].ShipToCode;

                        shipToCode = customerGateway.CustomerShipToView[0].ShipToCode;
                    }
                }

                return shipToCode;
            }
            catch
            {
                throw;
            }
        }

        #endregion

        #region Rating

        [Obsolete("Not used anymore")]
        public static string GetWebRatingComment(string ratingID, string customerCode)
        {
            string ratingComment = string.Empty;

            using (var con = DB.NewSqlConnection())
            {
                con.Open();
                using (var dr = DB.GetRSFormat(con, string.Format("select comments from EcommerceRating with (NOLOCK) where ratingid={0} and customercode={1}",
                                                                            ratingID, DB.SQuote(customerCode))))
                {
                    if (dr.Read())
                    {
                        ratingComment = DB.RSField(dr, "comments");
                    }
                }
            }

            return ratingComment;
        }

        #endregion

        public static void EnsurePricingHelperEntitiesAreInstantiated()
        {
        }

        public static string GetAccessoryProductsForMiniCart(string ItemCode)
        {
            String result = string.Empty;

            Customer thisCustomer = Customer.Current;

            List<XmlPackageParam> runtimeParams = new List<XmlPackageParam>();
            runtimeParams.Add(new XmlPackageParam("ItemCode", ItemCode));
            runtimeParams.Add(new XmlPackageParam("ContactCode", thisCustomer.ContactCode.ToString()));

            result = AppLogic.RunXmlPackage(
                        "page.minicart.accessory.xml.config",
                        null,
                        thisCustomer,
                        thisCustomer.SkinID,
                        String.Empty,
                        runtimeParams,
                        true,
                        true);

            return result;
        }

        #region GetActualSalesPrice

        public static decimal GetActualSalesPrice(string customerCode,
            string itemCode,
            string currencyCode)
        {
            return GetActualSalesPrice(customerCode,
                itemCode,
                currencyCode,
                decimal.One);
        }

        public static decimal GetActualSalesPrice(string customerCode,
            string itemCode,
            string currencyCode,
            decimal quantity)
        {
            return GetActualSalesPrice(customerCode,
                itemCode,
                currencyCode,
                quantity,
                string.Empty);
        }

        public static decimal GetActualSalesPrice(string customerCode,
            string itemCode,
            string currencyCode,
            decimal quantity,
            string unitMeasureCode)
        {
            bool withVat = AppLogic.AppConfigBool("VAT.Enabled") && AppLogic.AppConfigUSInt("VAT.DefaultSetting") == 1;

            return GetActualSalesPrice(customerCode,
                itemCode,
                currencyCode,
                quantity,
                unitMeasureCode,
                withVat);
        }

        /// <summary>
        /// Gets the actual sales price for the item
        /// If it has a promotional price, it returns it instead
        /// If it doesn't it returns the ordinary sales price
        /// </summary>
        /// <param name="customerCode"></param>
        /// <param name="itemCode"></param>
        /// <param name="currencyCode"></param>
        /// <param name="quantity"></param>
        /// <param name="unitMeasureCode"></param>
        /// <returns></returns>
        public static decimal GetActualSalesPrice(string customerCode,
            string itemCode,
            string currencyCode,
            decimal quantity,
            string unitMeasureCode,
            bool withVat)
        {
            decimal salesPrice = decimal.Zero;
            decimal promotionalPrice = decimal.Zero;
            decimal actualPrice = decimal.Zero;

            salesPrice = GetSalesPriceAndTax(customerCode, itemCode, currencyCode, quantity, unitMeasureCode, withVat, ref promotionalPrice);

            if (promotionalPrice != decimal.Zero)
            {
                actualPrice = promotionalPrice;
            }
            else
            {
                actualPrice = salesPrice;
            }

            return actualPrice;
        }
        #endregion

        #region GetSalesPrice

        public static decimal GetSalesPrice(string customerCode,
            int itemCounter,
            string currencyCode,
            decimal quantity,
            ref decimal promotionalPrice)
        {
            string itemCode = string.Empty;
            decimal salesprice = decimal.Zero;


            using (SqlConnection con = DB.NewSqlConnection())
            {
                con.Open();
                using (IDataReader reader = DB.GetRSFormat(con, "SELECT ItemCode FROM InventoryItem with (NOLOCK) WHERE Counter = {0}", itemCounter))
                {
                    if (reader.Read())
                    {
                        itemCode = DB.RSField(reader, "ItemCode");
                    }
                }
            }

            if (!string.IsNullOrEmpty(itemCode))
            {
                salesprice = GetSalesPrice(customerCode, itemCode, currencyCode, quantity, string.Empty, ref promotionalPrice);
            }

            return salesprice;
        }

        public static decimal GetSalesPrice(string customerCode,
            string itemCode,
            string currencyCode,
            decimal quantity,
            ref decimal promotionalPrice)
        {
            return GetSalesPrice(customerCode, itemCode, currencyCode, quantity, string.Empty, ref promotionalPrice);
        }

        public static decimal GetSalesPrice(string customerCode,
            string itemCode,
            string currencyCode,
            decimal quantity,
            string unitMeasureCode,
            ref decimal promotionalPrice)
        {

            return GetSalesPriceAndTax(customerCode,
                itemCode,
                currencyCode,
                quantity,
                unitMeasureCode,
                false,
                ref promotionalPrice);
        }

        public static decimal GetSalesPriceAndTax(string customerCode,
            string itemCode,
            string currencyCode,
            decimal quantity,
            string unitMeasureCode,
            bool withVAT,
            ref decimal promotionalPrice)
        {
            decimal vat = decimal.Zero;

            return GetSalesPriceAndTax(customerCode,
                itemCode,
                currencyCode,
                quantity,
                unitMeasureCode,
                withVAT,
                ref promotionalPrice,
                ref vat);
        }

        public static decimal GetSalesPriceAndTax(string customerCode,
            string itemCode,
            string currencyCode,
            decimal quantity,
            string unitMeasureCode,
            bool withVAT,
            ref decimal promotionalPrice,
            ref decimal vat)
        {
            return GetSalesPriceAndTax(customerCode,
                itemCode,
                currencyCode,
                quantity,
                unitMeasureCode,
                withVAT,
                ref promotionalPrice,
                ref vat,
                new UnitMeasureInfo() { Code = string.Empty }, null);
        }

        public static decimal GetSalesPriceAndTax(string customerCode,
           string itemCode,
           string currencyCode,
           decimal quantity,
           string unitMeasureCode,
           bool withVAT,
           ref decimal promotionalPrice,
           ref decimal vat,
           UnitMeasureInfo unitMeasureInfo, Customer customer)
        {
            UnitMeasureInfo umInfo = unitMeasureInfo;
            if (string.IsNullOrEmpty(unitMeasureInfo.Code))
            {
                umInfo = GetItemUnitMeasure(itemCode, unitMeasureCode);
            }

            decimal salesPrice = decimal.One;
            decimal regularPrice = decimal.Zero;
            decimal regularPriceRate = decimal.Zero;
            decimal promotionalPriceRate = decimal.Zero;
            decimal percent = decimal.Zero;
            decimal discount = decimal.Zero;
            decimal basePricingCost = decimal.Zero;
            decimal baseAverageCost = decimal.Zero;
            decimal categoryDiscount = decimal.Zero;
            string pricing = string.Empty;
            string customerItemDescription = string.Empty;
            string inventoryItemDescription = string.Empty;
            string customerItemCode = string.Empty;
            bool isSalePriceInBaseCurrency = false;
            bool isInventorySpecialPriceExpired = false;
            bool isCustomerSpecialPriceExpired = false;

            decimal matrixGroupQuantity = decimal.Zero;
            bool byTotalQty = false;

            try
            {
                // NOTE :
                //  If this is an anonymous customer, get the default pricing from the web config setting
                //  which usually would default to retail. Otherwise if the customer is logged in
                //  SalesOrderFacade.GetSalesPrice will try to retrieve internally whatever is
                //  the DefaultPricing specified on the customer.
                int id = 0;
                if (string.IsNullOrEmpty(customerCode) || int.TryParse(customerCode, out id))
                {
                    InterpriseSuiteEcommercePrincipal principal = null;
                    if (HttpContext.Current != null)
                    {
                        principal = HttpContext.Current.User as InterpriseSuiteEcommercePrincipal;
                    }

                    if (null != principal)
                    {
                        customerCode = principal.ThisCustomer.AnonymousCustomerCode;
                    }
                    else if (customer != null)
                    {
                        customerCode = customer.AnonymousCustomerCode;
                    }
                    else
                    {
                        Customer anonCustomer = Customer.MakeAnonymous();
                        customerCode = anonCustomer.AnonymousCustomerCode;
                    }
                }

                Interprise.Framework.Base.Shared.Enum.DefaultPricing defaultPricing = Interprise.Framework.Base.Shared.Enum.DefaultPricing.None;

                // NOTE :
                //  We will save the previous currency code
                //  since the parameter is passed by reference
                //  The procedure below can change the currency code
                //  based on the currency code not being present on the Inventory Selling Currency
                string resolvedCurrencyCode = currencyCode;

                BaseSalesOrderFacade.GetPrice(
                        customerCode,
                        ref resolvedCurrencyCode,
                        itemCode,
                        umInfo.Code,
                        umInfo.Quantity,
                        System.DateTime.Today,
                        quantity,
                        matrixGroupQuantity,
                        defaultPricing,
                        Interprise.Framework.Base.Shared.Enum.PricingMethod.None,
                        String.Empty,
                        ref byTotalQty,
                        ref salesPrice,
                        ref isSalePriceInBaseCurrency,
                        ref regularPrice,
                        ref promotionalPrice,
                        ref pricing,
                        ref percent,
                        ref discount,
                        ref categoryDiscount,
                        ref customerItemCode,
                        ref customerItemDescription,
                        ref inventoryItemDescription,
                        ref basePricingCost,
                        ref baseAverageCost,
                        ref isInventorySpecialPriceExpired,
                        ref isCustomerSpecialPriceExpired);

                //Get the regular price so we can compare it with promotional price.
                regularPriceRate = regularPrice;
                promotionalPriceRate = promotionalPrice;

                // NOTE :
                //  IsSalePriceInBaseCurrency = Flag to tell us whether we should
                //  perform conversion, this usually results in the CurrencyCode
                //  of this customer not defined in the Inventory Selling Language.
                //  Therefore GetSalesPrice will return the value based on the Home Currency
                //  Then we must perform conversion using the exchange rate of our 
                //  preferred currency code
                if (isSalePriceInBaseCurrency)
                {
                    decimal exchangeRate = Currency.GetExchangeRate(currencyCode);
                    decimal convertedRegularPrice =
                    Interprise.Facade.Base.SimpleFacade.Instance.ConvertCurrency(
                        exchangeRate,
                        salesPrice,
                        false,
                        currencyCode,
                        Interprise.Framework.Base.Shared.Enum.CurrencyFormat.Total
                    );

                    regularPriceRate = convertedRegularPrice;

                    // check if we have a promotional price
                    if (promotionalPrice != decimal.Zero)
                    {
                        promotionalPriceRate =
                        Interprise.Facade.Base.SimpleFacade.Instance.ConvertCurrency(
                            exchangeRate,
                            promotionalPrice,
                            false,
                            currencyCode,
                            Interprise.Framework.Base.Shared.Enum.CurrencyFormat.Total
                        );
                    }
                }

                // NOTE :
                //  If the customer Pricing Method is Markup on Last Cost
                //  The return value of GetSalesPrice wouldn't return the sales price
                //  as converted, we will have to do the conversion by ourselves
                //  manually
                bool customerPricingMethodIsMarkUpOnLastCost = CustomerPricingMethodIsMarkUpOnLastCost(customerCode);
                if (customerPricingMethodIsMarkUpOnLastCost)
                {
                    decimal exchangeRate = Currency.GetExchangeRate(currencyCode);

                    // if there's a Pricing Percent that's defined
                    // it's gonna be contained in the percent variable
                    decimal markUp = decimal.Zero;
                    if (percent > decimal.Zero)
                    {
                        markUp = (percent / 100M) * basePricingCost;

                        if (markUp > decimal.Zero)
                        {
                            basePricingCost += markUp;
                        }
                    }

                    decimal convertedRegularPrice =
                    Interprise.Facade.Base.SimpleFacade.Instance.ConvertCurrency(
                        exchangeRate,
                        basePricingCost,
                        false,
                        currencyCode,
                        Interprise.Framework.Base.Shared.Enum.CurrencyFormat.Total
                    );

                    regularPriceRate = convertedRegularPrice;
                }

                // Now let's see if we have a Discount by Band or OverAll
                decimal appliedDiscountPercent = decimal.Zero;

                if (discount > decimal.Zero)
                {
                    // It's OverAll disccount
                    appliedDiscountPercent = discount;
                }
                else if (categoryDiscount > decimal.Zero)
                {
                    appliedDiscountPercent = categoryDiscount;
                }

                if (appliedDiscountPercent > decimal.Zero)
                {
                    decimal appliedDiscount = (appliedDiscountPercent / 100M) * salesPrice;
                    salesPrice -= appliedDiscount;
                    regularPriceRate = salesPrice;
                }

                // now multiply by quantity
                salesPrice *= quantity;
                regularPriceRate *= quantity;
                promotionalPrice *= quantity;
                promotionalPriceRate *= quantity;

                bool computeVat = AppLogic.AppConfigBool("VAT.Enabled");
                if (computeVat)
                {
                    vat = ItemTaxFacade.CalculateTax(customerCode, itemCode, unitMeasureCode, salesPrice, regularPriceRate, basePricingCost, basePricingCost, quantity);
                    if (withVAT)
                    {
                        regularPriceRate += vat;
                    }

                    if (promotionalPrice != decimal.Zero)
                    {
                        vat = ItemTaxFacade.CalculateTax(customerCode, itemCode, unitMeasureCode, promotionalPrice, promotionalPriceRate, basePricingCost, basePricingCost, quantity);
                        if (withVAT)
                        {
                            promotionalPriceRate += vat;
                        }
                    }
                }

                promotionalPrice = promotionalPriceRate;
            }
            catch
            {
                throw;
            }
            return regularPriceRate;
        }

        public static decimal GetSalesPriceAndTax(string customerCode,
           string itemCode,
           string currencyCode,
           decimal quantity,
           string unitMeasureCode,
           bool withVAT,
           ref decimal promotionalPrice,
           ref decimal vat,
           UnitMeasureInfo unitMeasureInfo, Customer customer,
           ref decimal originalSalePrice, ref bool hasDiscount)
        {
            UnitMeasureInfo umInfo = unitMeasureInfo;
            if (string.IsNullOrEmpty(unitMeasureInfo.Code))
            {
                umInfo = GetItemUnitMeasure(itemCode, unitMeasureCode);
            }

            decimal salesPrice = decimal.One;
            decimal regularPrice = decimal.Zero;
            decimal regularPriceRate = decimal.Zero;
            decimal promotionalPriceRate = decimal.Zero;
            decimal percent = decimal.Zero;
            decimal discount = decimal.Zero;
            decimal basePricingCost = decimal.Zero;
            decimal baseAverageCost = decimal.Zero;
            decimal categoryDiscount = decimal.Zero;
            string pricing = string.Empty;
            string customerItemDescription = string.Empty;
            string inventoryItemDescription = string.Empty;
            string customerItemCode = string.Empty;
            bool isSalePriceInBaseCurrency = false;
            bool isInventorySpecialPriceExpired = false;
            bool isCustomerSpecialPriceExpired = false;

            decimal matrixGroupQuantity = decimal.Zero;
            bool byTotalQty = false;

            try
            {
                // NOTE :
                //  If this is an anonymous customer, get the default pricing from the web config setting
                //  which usually would default to retail. Otherwise if the customer is logged in
                //  SalesOrderFacade.GetSalesPrice will try to retrieve internally whatever is
                //  the DefaultPricing specified on the customer.
                int id = 0;
                if (string.IsNullOrEmpty(customerCode) || int.TryParse(customerCode, out id))
                {
                    InterpriseSuiteEcommercePrincipal principal = null;
                    if (HttpContext.Current != null)
                    {
                        principal = HttpContext.Current.User as InterpriseSuiteEcommercePrincipal;
                    }

                    if (null != principal)
                    {
                        customerCode = principal.ThisCustomer.AnonymousCustomerCode;
                    }
                    else if (customer != null)
                    {
                        customerCode = customer.AnonymousCustomerCode;
                    }
                    else
                    {
                        Customer anonCustomer = Customer.MakeAnonymous();
                        customerCode = anonCustomer.AnonymousCustomerCode;
                    }
                }

                Interprise.Framework.Base.Shared.Enum.DefaultPricing defaultPricing = Interprise.Framework.Base.Shared.Enum.DefaultPricing.None;

                // NOTE :
                //  We will save the previous currency code
                //  since the parameter is passed by reference
                //  The procedure below can change the currency code
                //  based on the currency code not being present on the Inventory Selling Currency
                string resolvedCurrencyCode = currencyCode;

                BaseSalesOrderFacade.GetPrice(
                        customerCode,
                        ref resolvedCurrencyCode,
                        itemCode,
                        umInfo.Code,
                        umInfo.Quantity,
                        System.DateTime.Today,
                        quantity,
                        matrixGroupQuantity,
                        defaultPricing,
                        Interprise.Framework.Base.Shared.Enum.PricingMethod.None,
                        String.Empty,
                        ref byTotalQty,
                        ref salesPrice,
                        ref isSalePriceInBaseCurrency,
                        ref regularPrice,
                        ref promotionalPrice,
                        ref pricing,
                        ref percent,
                        ref discount,
                        ref categoryDiscount,
                        ref customerItemCode,
                        ref customerItemDescription,
                        ref inventoryItemDescription,
                        ref basePricingCost,
                        ref baseAverageCost,
                        ref isInventorySpecialPriceExpired,
                        ref isCustomerSpecialPriceExpired);

                //Get the regular price so we can compare it with promotional price.
                regularPriceRate = regularPrice;
                promotionalPriceRate = promotionalPrice;

                originalSalePrice = regularPrice;

                // NOTE :
                //  IsSalePriceInBaseCurrency = Flag to tell us whether we should
                //  perform conversion, this usually results in the CurrencyCode
                //  of this customer not defined in the Inventory Selling Language.
                //  Therefore GetSalesPrice will return the value based on the Home Currency
                //  Then we must perform conversion using the exchange rate of our 
                //  preferred currency code
                if (isSalePriceInBaseCurrency)
                {
                    decimal exchangeRate = Currency.GetExchangeRate(currencyCode);
                    decimal convertedRegularPrice =
                    Interprise.Facade.Base.SimpleFacade.Instance.ConvertCurrency(
                        exchangeRate,
                        salesPrice,
                        false,
                        currencyCode,
                        Interprise.Framework.Base.Shared.Enum.CurrencyFormat.Total
                    );

                    regularPriceRate = convertedRegularPrice;
                    originalSalePrice = convertedRegularPrice;

                    // check if we have a promotional price
                    if (promotionalPrice != decimal.Zero)
                    {
                        promotionalPriceRate =
                        Interprise.Facade.Base.SimpleFacade.Instance.ConvertCurrency(
                            exchangeRate,
                            promotionalPrice,
                            false,
                            currencyCode,
                            Interprise.Framework.Base.Shared.Enum.CurrencyFormat.Total
                        );
                    }



                }

                // NOTE :
                //  If the customer Pricing Method is Markup on Last Cost
                //  The return value of GetSalesPrice wouldn't return the sales price
                //  as converted, we will have to do the conversion by ourselves
                //  manually
                bool customerPricingMethodIsMarkUpOnLastCost = CustomerPricingMethodIsMarkUpOnLastCost(customerCode);
                if (customerPricingMethodIsMarkUpOnLastCost)
                {
                    decimal exchangeRate = Currency.GetExchangeRate(currencyCode);

                    // if there's a Pricing Percent that's defined
                    // it's gonna be contained in the percent variable
                    decimal markUp = decimal.Zero;
                    if (percent > decimal.Zero)
                    {
                        markUp = (percent / 100M) * basePricingCost;

                        if (markUp > decimal.Zero)
                        {
                            basePricingCost += markUp;
                        }
                    }

                    decimal convertedRegularPrice =
                    Interprise.Facade.Base.SimpleFacade.Instance.ConvertCurrency(
                        exchangeRate,
                        basePricingCost,
                        false,
                        currencyCode,
                        Interprise.Framework.Base.Shared.Enum.CurrencyFormat.Total
                    );

                    regularPriceRate = convertedRegularPrice;

                    originalSalePrice = convertedRegularPrice;
                }

                // Now let's see if we have a Discount by Band or OverAll
                decimal appliedDiscountPercent = decimal.Zero;

                if (discount > decimal.Zero)
                {
                    // It's OverAll disccount
                    appliedDiscountPercent = discount;
                }
                else if (categoryDiscount > decimal.Zero)
                {
                    appliedDiscountPercent = categoryDiscount;
                }

                if (appliedDiscountPercent > decimal.Zero)
                {
                    hasDiscount = true;
                    decimal appliedDiscount = (appliedDiscountPercent / 100M) * salesPrice;
                    salesPrice -= appliedDiscount;
                    regularPriceRate = salesPrice;
                }

                // now multiply by quantity
                salesPrice *= quantity;
                regularPriceRate *= quantity;
                promotionalPrice *= quantity;
                promotionalPriceRate *= quantity;


                bool computeVat = AppLogic.AppConfigBool("VAT.Enabled");
                if (computeVat)
                {
                    vat = ItemTaxFacade.CalculateTax(customerCode, itemCode, unitMeasureCode, salesPrice, regularPriceRate, basePricingCost, basePricingCost, quantity);
                    if (withVAT)
                    {
                        regularPriceRate += vat;
                    }

                    if (promotionalPrice != decimal.Zero)
                    {
                        vat = ItemTaxFacade.CalculateTax(customerCode, itemCode, unitMeasureCode, promotionalPrice, promotionalPriceRate, basePricingCost, basePricingCost, quantity);
                        if (withVAT)
                        {
                            promotionalPriceRate += vat;
                        }
                    }
                }

                promotionalPrice = promotionalPriceRate;
            }
            catch
            {
                throw;
            }
            return regularPriceRate;
        }

        #endregion

        public static bool CustomerPricingMethodIsMarkUpOnLastCost(string customerCode)
        {
            bool customerPricingMethodIsMarkUpOnLastCost = false;
            using (var con = DB.NewSqlConnection())
            {
                con.Open();
                using (var reader = DB.GetRSFormat(con, "SELECT PricingMethod FROM Customer with (NOLOCK) WHERE CustomerCode = {0}", DB.SQuote(customerCode)))
                {
                    reader.Read();// must read
                    customerPricingMethodIsMarkUpOnLastCost = DB.RSField(reader, "PricingMethod").Equals(Interprise.Framework.Base.Shared.Const.TRANSACTION_PRICING_MARKUP);
                }
            }
            return customerPricingMethodIsMarkUpOnLastCost;
        }

        #region Upsell

        public enum ViewingPage
        {
            Product,
            ShoppingCart
        }

        public static string ShowInventoryAccessoryOptions(string itemCode,
            bool showPicture,
            int maxNumberOfRecordsToDisplay,
            string teaser,
            Customer thisCustomer,
            bool includeJavascript,
            bool includeDefaultContainer,
            ViewingPage page)
        {
            string displayFormat = AppLogic.AppConfig("AccessoryProductsFormat");
            string ImgFilename = string.Empty;
            bool existing = false;
            bool exists = false;

            StringBuilder tmpS = new StringBuilder();

            string customerCode = string.Empty;
            switch (page)
            {
                case ViewingPage.Product:
                    customerCode = CommonLogic.IIF(thisCustomer.IsNotRegistered, thisCustomer.AnonymousCustomerCode, thisCustomer.CustomerCode);
                    break;
                case ViewingPage.ShoppingCart:
                    customerCode = thisCustomer.CustomerCode;
                    break;
            }

            string query = string.Format("exec EcommerceGetAccessoryItems @CustomerCode = {0}, @WebSiteCode = {1}, @ItemCode = {2}, @LanguageCode = {3}, @CurrentDate = {4}, @ProductFilterID = {5}, @ContactCode = {6}",
                    DB.SQuote(customerCode),
                    DB.SQuote(ConfigInstance.WebSiteCode),
                    DB.SQuote(itemCode),
                    DB.SQuote(thisCustomer.LanguageCode),
                    DB.SQuote(Localization.DateTimeStringForDB(DateTime.Now)), 
                    DB.SQuote(thisCustomer.ProductFilterID), 
                    DB.SQuote(thisCustomer.ContactCode));

            DataSet ds = DB.GetDS(query, false);

            if (ds.Tables[0].Rows.Count > 0)
            {
                if (includeJavascript)
                {
                    tmpS.Append("\n");
                    tmpS.Append("<script type=\"text/javascript\" Language=\"JavaScript\">\n");
                    tmpS.Append("function UpsellClick(theItem)\n");
                    tmpS.Append("	{\n");
                    tmpS.Append("	var UpsellItemList = '';\n");
                    tmpS.Append("	var whichitem = 0;\n");
                    tmpS.Append("	var theForm = document.forms['UpsellForm'];\n");
                    tmpS.Append("	while (whichitem < theForm.Upsell.length)\n");
                    tmpS.Append("	{\n");
                    tmpS.Append("		if (theForm.Upsell[whichitem].checked && theForm.Upsell[whichitem].value != '0')\n");
                    tmpS.Append("		{\n");
                    tmpS.Append("			if (UpsellItemList.length > 0)\n");
                    tmpS.Append("			{\n");
                    tmpS.Append("				UpsellItemList = UpsellItemList + ',';\n");
                    tmpS.Append("			}\n");
                    tmpS.Append("			UpsellItemList = UpsellItemList + theForm.Upsell[whichitem].value;\n");
                    tmpS.Append("		}\n");
                    tmpS.Append("		whichitem++;\n");
                    tmpS.Append("	}\n");
                    tmpS.Append("	if (UpsellItemList.length > 0)\n"); // set all upsell hidden fields on all addtocart forms, so they are picked up on a submit:
                    tmpS.Append("	{\n");
                    tmpS.Append("		var whichform = 0;\n");
                    tmpS.Append("		while (whichform < document.forms.length)\n");
                    tmpS.Append("		{\n");
                    tmpS.Append("			if(document.forms[whichform].UpsellProducts != null)\n");
                    tmpS.Append("			{\n");
                    tmpS.Append("				document.forms[whichform].UpsellProducts.value = UpsellItemList;\n");
                    tmpS.Append("			}\n");
                    tmpS.Append("			whichform++;\n");
                    tmpS.Append("		}\n");
                    tmpS.Append("	}\n");
                    tmpS.Append("	}\n");
                    tmpS.Append("</script>\n");

                    tmpS.Append("<form id=\"UpsellForm\" name=\"UpsellForm\" style=\"margin-top: 0px; margin-bottom: 0px;\">");

                    tmpS.Append("<input style=\"visibility: hidden;\" type=\"checkbox\" id=\"Upsell\" name=\"Upsell\" value=\"0\" onClick=\"UpsellClick(this);\">"); // must have at least 2 checkboxes for javascript to work!
                }


                if (includeDefaultContainer)
                {
                    tmpS.Append("<table width=\"100%\" cellpadding=\"2\" cellspacing=\"0\" border=\"0\" style=\"border-style: solid; border-width: 0px; border-color: #" + AppLogic.AppConfig("HeaderBGColor") + "\">\n");
                    tmpS.Append("<tr><td align=\"left\" valign=\"top\">\n");
                    tmpS.Append("<span class=\"UpsellSectionLabel\"> " + AppLogic.GetString("showproduct.aspx.51", thisCustomer.SkinID, thisCustomer.LocaleSetting) + " </span>");
                    tmpS.Append("<table width=\"100%\" cellpadding=\"4\" cellspacing=\"0\" border=\"0\" style=\"" + AppLogic.AppConfig("BoxFrameStyle") + "\">\n");
                    tmpS.Append("<tr><td align=\"left\" valign=\"top\" class=\"UpsellProductsBoxStyle\">\n");
                }

                if (teaser.Length != 0)
                {
                    tmpS.Append("<p><b>" + teaser + "</b></p>\n");
                }
                else
                {
                    Topic UpsellTeaser = new Topic("UpsellTeaser", thisCustomer.LocaleSetting, thisCustomer.SkinID, null);
                    tmpS.Append(UpsellTeaser.Contents.Replace("(!SKINID!)", thisCustomer.SkinID.ToString()));
                }

                try
                {
                    bool empty = (ds.Tables[0].Rows.Count > 0);

                    bool isWholesaleOnlySite =
                        AppLogic.AppConfigBool("WholesaleOnlySite") &&
                        thisCustomer.DefaultPrice.ToLower() != "wholesale";

                    switch (displayFormat.ToUpperInvariant())
                    {
                        // Render in Grid format ---------------------------------------------------->
                        case "GRID":
                            int ItemNumber = 1;
                            int ItemsPerRow = AppLogic.AppConfigUSInt("AccessoryGridColWidth");

                            if (ItemsPerRow == 0)
                            {
                                ItemsPerRow = 4;
                            }
                            tmpS.Append("<table border=\"0\" cellpadding=\"0\" cellspacing=\"4\" width=\"100%\">");
                            foreach (DataRow row in ds.Tables[0].Rows)
                            {
                                ImgFilename = "";
                                bool validAccessory = true;
                                if (DB.RowField(row, "ItemType") == Interprise.Framework.Base.Shared.Const.ITEM_TYPE_ELECTRONIC_DOWNLOAD)
                                {
                                    DownloadableItem download = DownloadableItem.FindByItemCode(DB.RowField(row, "AccessoryCode"));
                                    if (null == download)
                                    {
                                        validAccessory = false;
                                    }
                                    else
                                    {
                                        if (!download.IsPhysicalFileExisting())
                                        {
                                            validAccessory = false;
                                        }
                                    }
                                }

                                string displayName = DB.RowField(row, "ItemDescription");
                                if (CommonLogic.IsStringNullOrEmpty(displayName))
                                {
                                    displayName = DB.RowField(row, "AccessoryName");
                                }

                                if (ItemNumber == 1)
                                {
                                    tmpS.Append("<tr>");
                                }
                                if (ItemNumber == ItemsPerRow + 1)
                                {
                                    tmpS.Append("</tr><tr><td colspan=\"" + ItemsPerRow.ToString() + "\" height=\"8\"></td></tr>");
                                    ItemNumber = 1;
                                }
                                tmpS.Append("<td width=\"" + (100 / ItemsPerRow).ToString() + "%\" height=\"150\" align=\"center\" valign=\"bottom\">");
                            
                                if (showPicture)
                                {
                                    String ImgUrl = String.Empty;
                                    XSLTExtensionBase xslt = new XSLTExtensionBase(thisCustomer, thisCustomer.SkinID);
                                    ImgUrl = xslt.DisplayImage("Product", DB.RowField(row, "Counter"), "icon");

                                    if (ImgUrl.Length != 0)
                                    {
                                        tmpS.Append("<a href=\"" + SE.MakeProductLink(DB.RowFieldInt(row, "Counter").ToString(), displayName) + "\">");
                                        tmpS.Append(ImgUrl);
                                        tmpS.Append("</a>");
                                        tmpS.Append("<br /><br />");
                                    }
                                }
                                tmpS.Append("<a title=\"" + displayName + "\" href=\"" + SE.MakeProductLink(DB.RowFieldInt(row, "Counter").ToString(), CommonLogic.Ellipses(displayName, 20, false)) + "\">");
                                tmpS.Append(AppLogic.MakeProperObjectName(DB.RowFieldInt(row, "Counter").ToString(), Security.HtmlEncode(CommonLogic.Ellipses(displayName, 20, false)), thisCustomer.LocaleSetting));
                                tmpS.Append("</a>");
                                tmpS.Append("<br/>");
                                if (!isWholesaleOnlySite)
                                {
                                    bool withVat = AppLogic.AppConfigBool("VAT.Enabled") && thisCustomer.VATSettingReconciled == VatDefaultSetting.Inclusive;
                                    decimal promotionalPrice = decimal.Zero;

                                    UnitMeasureInfo um = UnitMeasureInfo.ForItem(DB.RowField(row, "AccessoryCode"), UnitMeasureInfo.ITEM_DEFAULT);
                                    decimal price = InterpriseHelper.GetSalesPriceAndTax(thisCustomer.CustomerCode, DB.RowField(row, "AccessoryCode"), thisCustomer.CurrencyCode, decimal.One, um.Code, withVat, ref promotionalPrice);
                                    if (promotionalPrice != decimal.Zero)
                                    {
                                        price = promotionalPrice;
                                    }

                                    tmpS.Append(FormatCurrencyForCustomer(price, thisCustomer.CurrencyCode));
                                    if (AppLogic.AppConfigBool("VAT.Enabled"))
                                    {
                                        if (thisCustomer.VATSettingReconciled == VatDefaultSetting.Inclusive)
                                        {
                                            tmpS.Append(" <span class=\"VATLabel\">" + AppLogic.GetString("showproduct.aspx.38", thisCustomer.SkinID, thisCustomer.LocaleSetting) + "</span>\n");
                                        }
                                        else
                                        {
                                            tmpS.Append(" <span class=\"VATLabel\">" + AppLogic.GetString("showproduct.aspx.37", thisCustomer.SkinID, thisCustomer.LocaleSetting) + "</span>\n");
                                        }
                                    }
                                }
                                tmpS.Append("<br/>");
                                tmpS.Append("<input type=\"checkbox\" id=\"Upsell\" name=\"Upsell\" value=\"" + DB.RowFieldInt(row, "Counter") + "\" " + CommonLogic.IIF(includeJavascript, "onClick=\"UpsellClick(this);\"", string.Empty) + " " + CommonLogic.IIF(validAccessory, string.Empty, "disabled=\"true\"") + ">");
                                tmpS.Append("<span class=\"IncludeToCart\"> " + AppLogic.GetString("showproduct.aspx.58", thisCustomer.SkinID, thisCustomer.LocaleSetting) + " </span>\n");
                                tmpS.Append("</td>");
                                ItemNumber++;
                            }

                            for (int ctr = ItemNumber; ctr <= ItemsPerRow; ctr++)
                            {
                                tmpS.Append("<td>&nbsp;</td>");
                            }
                            tmpS.Append("</tr>");
                            tmpS.Append("</table>");
                            break;

                        // Render in table format ---------------------------------------------------->
                        case "TABLE":
                            tmpS.Append("<table width=\"100%\" cellpadding=\"2\" cellspacing=\"0\" border=\"0\">\n");
                            int i = 1;
                            foreach (DataRow row in ds.Tables[0].Rows)
                            {
                                bool validAccessory = true;
                                if (DB.RowField(row, "ItemType") == Interprise.Framework.Base.Shared.Const.ITEM_TYPE_ELECTRONIC_DOWNLOAD)
                                {
                                    DownloadableItem download = DownloadableItem.FindByItemCode(DB.RowField(row, "AccessoryCode"));
                                    if (null == download)
                                    {
                                        validAccessory = false;
                                    }

                                    if (!download.IsPhysicalFileExisting())
                                    {
                                        validAccessory = false;
                                    }
                                }

                                string displayName = DB.RowField(row, "ItemDescription");
                                if (string.IsNullOrEmpty(displayName))
                                {
                                    displayName = DB.RowField(row, "AccessoryName");
                                }

                                if (i > maxNumberOfRecordsToDisplay)
                                {
                                    tmpS.Append("<tr><td " + CommonLogic.IIF(showPicture, "colspan=\"2\"", "") + "><hr size=\"1\" class=\"LightCellText\"/></td></tr>");
                                    break;
                                }
                                if (i > 1)
                                {
                                    tmpS.Append("<tr><td " + CommonLogic.IIF(showPicture, "colspan=\"2\"", "") + "><hr size=\"1\" class=\"LightCellText\"/></td></tr>");
                                }
                                tmpS.Append("<tr>");
                                String ImgUrl = String.Empty;
                                string accItemCode = InterpriseHelper.GetInventoryItemCode(DB.RowFieldInt(row, "Counter"));
                                using (SqlConnection con = DB.NewSqlConnection())
                                {
                                    con.Open();
                                    using (IDataReader reader = DB.GetRSFormat(con, "SELECT Filename FROM InventoryOverrideImage with (NOLOCK) WHERE ItemCode = {0} AND WebSiteCode = {1} AND IsDefaultIcon = 1", DB.SQuote(accItemCode), DB.SQuote(InterpriseHelper.ConfigInstance.WebSiteCode)))
                                    {
                                        existing = reader.Read();
                                        if (existing)
                                        {
                                            ImgFilename = (DB.RSField(reader, "Filename"));
                                        }
                                    }
                                }
                                ImgUrl = AppLogic.LocateImageFilenameUrl("Product", accItemCode, "icon", ImgFilename, AppLogic.AppConfigBool("Watermark.Enabled"), out exists);

                                if (showPicture)
                                {
                                    string seTitle = "";
                                    string seAltText = "";
                                    AppLogic.GetSEImageAttributes(accItemCode, "icon", thisCustomer.LanguageCode, ref seTitle, ref seAltText);

                                    tmpS.Append("<td align=\"left\" valign=\"top\">\n");
                                    tmpS.Append("<a href=\"" + SE.MakeProductLink(DB.RowFieldInt(row, "Counter").ToString(), displayName) + "\">");
                                    tmpS.Append("<img align=\"left\" src=\"" + ImgUrl + "\" border=\"0\" alt=\"" + seAltText + "\" title=\"" + seTitle + "\" />");
                                    tmpS.Append("</a>");
                                    tmpS.Append("</td>");
                                }

                                tmpS.Append("<td align=\"left\" valign=\"top\">\n");
                                tmpS.Append("<b class=\"a4\">");
                                tmpS.Append("<a title=\"" + displayName + "\" href=\"" + SE.MakeProductLink(DB.RowFieldInt(row, "Counter").ToString(), CommonLogic.Ellipses(displayName, 20, false)) + "\">");
                                tmpS.Append(AppLogic.MakeProperObjectName(DB.RowFieldInt(row, "Counter").ToString(), Security.HtmlEncode(CommonLogic.Ellipses(displayName, 20, false)), thisCustomer.LocaleSetting));
                                tmpS.Append("</a></b><br />\n");

                                if (DB.RowFieldByLocale(row, "WebDescription", thisCustomer.LocaleSetting).Length != 0)
                                {
                                    String tmpD = DB.RowFieldByLocale(row, "WebDescription", thisCustomer.LocaleSetting);
                                    if (AppLogic.ReplaceImageURLFromAssetMgr)
                                    {
                                        tmpD = tmpD.Replace("../images", "images");
                                    }
                                    tmpS.Append("<span class=\"a2\">" + tmpD + "</span><br />\n");
                                }
                                else
                                {
                                    if (DB.RowFieldByLocale(row, "ItemDescription", thisCustomer.LocaleSetting).Length != 0)
                                    {
                                        tmpS.Append("<span class=\"a2\">" + DB.RowFieldByLocale(row, "ItemDescription", thisCustomer.LocaleSetting) + "</span><br />\n");
                                    }
                                }

                                tmpS.Append("<div class=\"a1\" style=\"PADDING-BOTTOM: 10px; PADDING-TOP: 10px;\">\n");
                                tmpS.Append("<a href=\"" + SE.MakeProductLink(DB.RowFieldInt(row, "Counter").ToString(), displayName) + "\">");
                                tmpS.Append(AppLogic.GetString("common.cs.9", thisCustomer.SkinID, Thread.CurrentThread.CurrentUICulture.Name));
                                tmpS.Append("</a>");
                                tmpS.Append("</div>\n");
                                tmpS.Append("<br/>\n");

                                if (!isWholesaleOnlySite)
                                {
                                    bool withVat = AppLogic.AppConfigBool("VAT.Enabled") && thisCustomer.VATSettingReconciled == VatDefaultSetting.Inclusive;
                                    decimal promotionalPrice = decimal.Zero;

                                    UnitMeasureInfo um = UnitMeasureInfo.ForItem(DB.RowField(row, "AccessoryCode"), UnitMeasureInfo.ITEM_DEFAULT);
                                    decimal price = InterpriseHelper.GetSalesPriceAndTax(thisCustomer.CustomerCode, DB.RowField(row, "AccessoryCode"), thisCustomer.CurrencyCode, decimal.One, um.Code, withVat, ref promotionalPrice);
                                    if (promotionalPrice != decimal.Zero)
                                    {
                                        price = promotionalPrice;
                                    }

                                    tmpS.Append(FormatCurrencyForCustomer(price, thisCustomer.CurrencyCode));
                                    if (AppLogic.AppConfigBool("VAT.Enabled"))
                                    {
                                        if (thisCustomer.VATSettingReconciled == VatDefaultSetting.Inclusive)
                                        {
                                            tmpS.Append(" <span class=\"VATLabel\">" + AppLogic.GetString("showproduct.aspx.38", thisCustomer.SkinID, thisCustomer.LocaleSetting) + "</span>\n");
                                        }
                                        else
                                        {
                                            tmpS.Append(" <span class=\"VATLabel\">" + AppLogic.GetString("showproduct.aspx.37", thisCustomer.SkinID, thisCustomer.LocaleSetting) + "</span>\n");
                                        }
                                    }
                                }

                                tmpS.Append("<br/>");
                                tmpS.Append("<input type=\"checkbox\" id=\"Upsell\" name=\"Upsell\" value=\"" + DB.RowFieldInt(row, "Counter") + "\" " + CommonLogic.IIF(includeJavascript, "onClick=\"UpsellClick(this);\"", string.Empty) + " " + CommonLogic.IIF(validAccessory, string.Empty, "disabled=\"true\"") + ">");
                                tmpS.Append("<span class=\"IncludeToCart\"> " + AppLogic.GetString("showproduct.aspx.58", thisCustomer.SkinID, thisCustomer.LocaleSetting) + " </span>\n");
                                tmpS.Append("</td>");
                                tmpS.Append("</tr>");
                                i++;
                            }
                            tmpS.Append("</table>\n");
                            break;
                    }
                }
                catch
                {

                }

                if (includeDefaultContainer)
                {
                    tmpS.Append("</td></tr>\n");
                    tmpS.Append("</table>\n");
                    tmpS.Append("</td></tr>\n");
                    tmpS.Append("</table>\n");
                }

                if (includeJavascript)
                {
                    tmpS.Append("</form>\n");
                }
            }
            ds.Dispose();

            return tmpS.ToString();

        }

        //Customers Who Purchased This Item Also Purchased Section
        public static string ShowAlsoPurchasedProducts(string itemCode, bool shouldShowPicture, Customer thisCustomer)
        {
                bool shouldShowPurchasedFromSameCategory = false;
                string categoryCode = string.Empty;
                ArrayList arrCat = new ArrayList();
                StringBuilder tmpS = new StringBuilder();
               
                if (AppLogic.AppConfigBool("CustomersWhoPurchasedThisItemAlsoPurchased.Enabled"))
                {
                    using (SqlConnection con = DB.NewSqlConnection())
                    {
                        con.Open();
                        using (IDataReader reader = DB.GetRSFormat(con, "SELECT IIW.ShowPurchasedItemsFromSameCategory, IC.CategoryCode FROM InventoryItemWebOption IIW with (NOLOCK) LEFT JOIN InventoryCategory IC with (NOLOCK) ON IC.ItemCode = IIW.ItemCode WHERE IIW.ItemCode = {0}", DB.SQuote(itemCode)))
                        {
                            while (reader.Read())
                            {
                                shouldShowPurchasedFromSameCategory = DB.RSFieldBool(reader, "ShowPurchasedItemsFromSameCategory");
                                if (shouldShowPurchasedFromSameCategory)
                                {
                                    categoryCode = DB.RSField(reader, "CategoryCode");
                                    if (categoryCode == string.Empty)
                                    {
                                        categoryCode = "00000";
                                    }
                                    arrCat.Add(categoryCode);
                                }

                            }
                        }
                    }
                    string categories = string.Join(",", arrCat.ToArray());
                    int ProductsToDisplay = AppLogic.AppConfigUSInt("PurchasedProductsToDisplay");
                    if (ProductsToDisplay == 0)
                    {
                        ProductsToDisplay = 8;
                    }

                    string query = string.Format("exec EcommerceGetPurchasedProducts  @WebSiteCode = {0}, @ItemCode = {1}, @CategoryCode = {2}, @LanguageCode = {3}, @CurrentDate = {4}, @ProductsToDisplay = {5}, @ProductFilterID = {6}, @ContactCode = {7}",
                                        DB.SQuote(ConfigInstance.WebSiteCode),
                                        DB.SQuote(itemCode),
                                        DB.SQuote(categories),
                                        DB.SQuote(thisCustomer.LanguageCode),
                                        DB.SQuote(Localization.DateTimeStringForDB(DateTime.Now)),
                                        ProductsToDisplay,
                                        DB.SQuote(thisCustomer.ProductFilterID),
                                        DB.SQuote(thisCustomer.ContactCode));

                    using (SqlConnection con = DB.NewSqlConnection())
                    {
                        con.Open();
                        using (IDataReader dr = DB.GetRSFormat(con, query))
                        {
                            if (((SqlDataReader)dr).HasRows)
                            {
                                tmpS.Append("<br/><br/>");
                                tmpS.Append(" <span class=\"UpsellSectionLabel\"> " + AppLogic.GetString("showproduct.aspx.52", thisCustomer.SkinID, thisCustomer.LocaleSetting) + " </span>");
                                tmpS.Append("<hr style= \"border-top-color:black; border-top-width: 1px; border-top-style:solid;\" width=\"100%\">");
                                tmpS.Append("<div class= \"also-Purchased-pagination\"></div>");
                                tmpS.Append("<div class=\"carousel\" id=\"also-Purchased\">");
                                tmpS.Append("<div class=\"wrapper3\" style=\"overflow: hidden;\" id = \"purchased\">");
                                tmpS.Append("<ul id=\"purchased-items\">");

                                try
                                {
                                    bool isWholesaleOnlySite =
                                        AppLogic.AppConfigBool("WholesaleOnlySite") &&
                                        thisCustomer.DefaultPrice.ToLower() != "wholesale";
                                    {
                                        while (dr.Read())
                                        {
                                            string displayName = DB.RSField(dr, "ItemDescription");
                                            if (CommonLogic.IsStringNullOrEmpty(displayName))
                                            {
                                                displayName = DB.RSField(dr, "ItemName");
                                            }

                                            tmpS.Append("<li class= \"also-Purchased-item\">");
                                            tmpS.Append("<center>");

                                            if (shouldShowPicture)
                                            {
                                                String ImgUrl = String.Empty;
                                                XSLTExtensionBase xslt = new XSLTExtensionBase(thisCustomer, thisCustomer.SkinID);
                                                ImgUrl = xslt.DisplayImage("Product", Convert.ToString(DB.RSFieldInt(dr, "Counter")), "icon");

                                                if (ImgUrl.Length != 0)
                                                {
                                                    tmpS.Append("<a href=\"" + SE.MakeProductLink(DB.RSFieldInt(dr, "Counter").ToString(), displayName) + "\">");
                                                    tmpS.Append(ImgUrl);
                                                    tmpS.Append("</a>");
                                                    tmpS.Append("<br />");
                                                }
                                            }
                                            tmpS.Append("<a title=\"" + displayName + "\" href=\"" + SE.MakeProductLink(DB.RSFieldInt(dr, "Counter").ToString(), CommonLogic.Ellipses(displayName, 20, false)) + "\" >");
                                            tmpS.Append(AppLogic.MakeProperObjectName(DB.RSFieldInt(dr, "Counter").ToString(), Security.HtmlEncode(CommonLogic.Ellipses(displayName, 20, false)), thisCustomer.LocaleSetting));
                                            tmpS.Append("</a>");
                                            tmpS.Append("<br/>");
                                            if (!isWholesaleOnlySite)
                                            {
                                                bool withVat = AppLogic.AppConfigBool("VAT.Enabled") && thisCustomer.VATSettingReconciled == VatDefaultSetting.Inclusive;
                                                decimal promotionalPrice = decimal.Zero;

                                                UnitMeasureInfo um = UnitMeasureInfo.ForItem(DB.RSField(dr, "ItemCode"), UnitMeasureInfo.ITEM_DEFAULT);
                                                decimal price = InterpriseHelper.GetSalesPriceAndTax(thisCustomer.CustomerCode, DB.RSField(dr, "ItemCode"), thisCustomer.CurrencyCode, decimal.One, um.Code, withVat, ref promotionalPrice);
                                                if (promotionalPrice != decimal.Zero)
                                                {
                                                    price = promotionalPrice;
                                                }

                                                tmpS.Append(FormatCurrencyForCustomer(price, thisCustomer.CurrencyCode));
                                                if (AppLogic.AppConfigBool("VAT.Enabled"))
                                                {
                                                    if (thisCustomer.VATSettingReconciled == VatDefaultSetting.Inclusive)
                                                    {
                                                        tmpS.Append(" <span class=\"VATLabel\">" + AppLogic.GetString("showproduct.aspx.38", thisCustomer.SkinID, thisCustomer.LocaleSetting) + "</span>");
                                                    }
                                                    else
                                                    {
                                                        tmpS.Append(" <span class=\"VATLabel\">" + AppLogic.GetString("showproduct.aspx.37", thisCustomer.SkinID, thisCustomer.LocaleSetting) + "</span>");
                                                    }
                                                }
                                            }
                                            tmpS.Append("<br/>");
                                            tmpS.Append("<input type=\"checkbox\" id=\"" + DB.RSFieldInt(dr, "Counter") + "\" onClick=\"NewUpsellClick(this);\">");
                                            tmpS.Append("<span class=\"IncludeToCart\"> " + AppLogic.GetString("showproduct.aspx.58", thisCustomer.SkinID, thisCustomer.LocaleSetting) + " </span>\n");
                                            tmpS.Append("</center>");
                                            tmpS.Append("</li>");
                                        }
                                    }
                                }
                                catch
                                {
                                }
                                tmpS.Append("</ul>");
                                tmpS.Append("</div>");
                                tmpS.Append("</div>");
                            }
                        }
                    }
                }
                return tmpS.ToString();
        }

        //Customers Who Viewed This Item Also Viewed Section
        public static string ShowAlsoViewedProducts(string itemCode, bool shouldShowPicture, Customer thisCustomer)
        {
            bool shouldShowViewedFromSameCategory = false;
            string categoryCode = string.Empty;
            ArrayList arrCat = new ArrayList();
            StringBuilder tmpS = new StringBuilder();
           
            if (AppLogic.AppConfigBool("CustomersWhoViewedThisItemAlsoViewed.Enabled"))
            {
                using (SqlConnection con = DB.NewSqlConnection())
                {
                    con.Open();
                    using (IDataReader reader = DB.GetRSFormat(con, "SELECT IIW.ShowViewedItemsFromSameCategory, IC.CategoryCode FROM InventoryItemWebOption IIW with (NOLOCK) LEFT JOIN InventoryCategory IC with (NOLOCK) ON IIW.ItemCode = IC.ItemCode WHERE IIW.ItemCode = {0}", DB.SQuote(itemCode)))
                    {
                        while (reader.Read())
                        {
                            shouldShowViewedFromSameCategory = DB.RSFieldBool(reader, "ShowViewedItemsFromSameCategory");
                            if (shouldShowViewedFromSameCategory)
                            {
                                categoryCode = DB.RSField(reader, "CategoryCode");
                                if (categoryCode == string.Empty)
                                {
                                    categoryCode = "00000";
                                }
                                arrCat.Add(categoryCode);
                            }
                        }
                    }
                }
                
                string categories = string.Join(",", arrCat.ToArray());
                int ProductsToDisplay = AppLogic.AppConfigUSInt("ViewedProductsToDisplay");
                if (ProductsToDisplay == 0)
                {
                    ProductsToDisplay = 8;
                }
                string query = string.Format("exec EcommerceGetViewedProducts  @WebSiteCode = {0}, @ItemCode = {1}, @CategoryCode = {2}, @LanguageCode = {3}, @CurrentDate = {4}, @ProductsToDisplay = {5}, @ProductFilterID = {6}, @ContactCode = {7}",
                                    DB.SQuote(ConfigInstance.WebSiteCode),
                                    DB.SQuote(itemCode),
                                    DB.SQuote(categories),
                                    DB.SQuote(thisCustomer.LanguageCode),
                                    DB.SQuote(Localization.DateTimeStringForDB(DateTime.Now)),
                                    ProductsToDisplay,
                                    DB.SQuote(thisCustomer.ProductFilterID),
                                    DB.SQuote(thisCustomer.ContactCode));


                using (SqlConnection con = DB.NewSqlConnection())
                {
                    con.Open();
                    using (IDataReader dr = DB.GetRSFormat(con, query))
                    {
                        //if (dr.RecordsAffected > 0)
                        if (((SqlDataReader)dr).HasRows)
                        {
                            tmpS.Append("<br /><br />");
                            tmpS.Append(" <span class=\"UpsellSectionLabel\"> " + AppLogic.GetString("showproduct.aspx.53", thisCustomer.SkinID, thisCustomer.LocaleSetting) + " </span>\n");
                            tmpS.Append("<hr style= \"border-top-color:black; border-top-width: 1px; border-top-style:solid;\" width=\"100%\">");
                            tmpS.Append("<div class= \"also-Viewed-pagination\"></div>");
                            tmpS.Append("<div class=\"carousel\" id=\"also-Viewed\">");
                            tmpS.Append("<div class=\"wrapper3\" style=\"overflow: hidden;\" id = \"viewed\">");
                            tmpS.Append("<ul id=\"viewed-items\">");

                            try
                            {
                                bool isWholesaleOnlySite =
                                    AppLogic.AppConfigBool("WholesaleOnlySite") &&
                                    thisCustomer.DefaultPrice.ToLower() != "wholesale";
                                {
                                    while (dr.Read())
                                    {
                                        string displayName = DB.RSField(dr, "ItemDescription");
                                        if (CommonLogic.IsStringNullOrEmpty(displayName))
                                        {
                                            displayName = DB.RSField(dr, "ItemName");
                                        }

                                        tmpS.Append("<li class= \"also-Viewed-item\">");
                                        tmpS.Append("<center>");

                                        if (shouldShowPicture)
                                        {
                                            String ImgUrl = String.Empty;
                                            XSLTExtensionBase xslt = new XSLTExtensionBase(thisCustomer, thisCustomer.SkinID);
                                            ImgUrl = xslt.DisplayImage("Product", Convert.ToString(DB.RSFieldInt(dr, "Counter")), "icon");

                                            if (ImgUrl.Length != 0)
                                            {
                                                tmpS.Append("<a href=\"" + SE.MakeProductLink(DB.RSFieldInt(dr, "Counter").ToString(), displayName) + "\">");
                                                tmpS.Append(ImgUrl);
                                                tmpS.Append("</a>");
                                                tmpS.Append("<br />");
                                            }
                                        }
                                        tmpS.Append("<a title=\"" + displayName + "\" href=\"" + SE.MakeProductLink(DB.RSFieldInt(dr, "Counter").ToString(), CommonLogic.Ellipses(displayName, 20, false)) + "\" >");
                                        tmpS.Append(AppLogic.MakeProperObjectName(DB.RSFieldInt(dr, "Counter").ToString(), Security.HtmlEncode(CommonLogic.Ellipses(displayName, 20, false)), thisCustomer.LocaleSetting));
                                        tmpS.Append("</a>");
                                        tmpS.Append("<br/>");
                                        if (!isWholesaleOnlySite)
                                        {
                                            bool withVat = AppLogic.AppConfigBool("VAT.Enabled") && thisCustomer.VATSettingReconciled == VatDefaultSetting.Inclusive;
                                            decimal promotionalPrice = decimal.Zero;

                                            UnitMeasureInfo um = UnitMeasureInfo.ForItem(DB.RSField(dr, "ItemCode"), UnitMeasureInfo.ITEM_DEFAULT);
                                            decimal price = InterpriseHelper.GetSalesPriceAndTax(thisCustomer.CustomerCode, DB.RSField(dr, "ItemCode"), thisCustomer.CurrencyCode, decimal.One, um.Code, withVat, ref promotionalPrice);
                                            if (promotionalPrice != decimal.Zero)
                                            {
                                                price = promotionalPrice;
                                            }

                                            tmpS.Append(FormatCurrencyForCustomer(price, thisCustomer.CurrencyCode));
                                            if (AppLogic.AppConfigBool("VAT.Enabled"))
                                            {
                                                if (thisCustomer.VATSettingReconciled == VatDefaultSetting.Inclusive)
                                                {
                                                    tmpS.Append(" <span class=\"VATLabel\">" + AppLogic.GetString("showproduct.aspx.38", thisCustomer.SkinID, thisCustomer.LocaleSetting) + "</span>");
                                                }
                                                else
                                                {
                                                    tmpS.Append(" <span class=\"VATLabel\">" + AppLogic.GetString("showproduct.aspx.37", thisCustomer.SkinID, thisCustomer.LocaleSetting) + "</span>");
                                                }
                                            }
                                        }

                                        tmpS.Append("<br/>");
                                        tmpS.Append("<input type=\"checkbox\" id=\"" + DB.RSFieldInt(dr, "Counter") + "\" onClick=\"NewUpsellClick(this);\">");
                                        tmpS.Append("<span class=\"IncludeToCart\"> " + AppLogic.GetString("showproduct.aspx.58", thisCustomer.SkinID, thisCustomer.LocaleSetting) + " </span>\n");
                                        tmpS.Append("</center>");
                                        tmpS.Append("</li>");
                                    }
                                }
                            }
                            catch
                            {
                            }
                            tmpS.Append("</ul>");
                            tmpS.Append("</div>");
                            tmpS.Append("</div>");
                        }
                    }
                }
            }
            return tmpS.ToString();
        }

        #endregion

        #region Order

        public static string FormatCurrencyForCustomer(decimal amount, string customerCurrencyCode)
        {
            var currencyFormat = Currency.GetCurrencyFormat(customerCurrencyCode);
            return amount.ToString("C", currencyFormat);
        }

        public static string SerializeMaxMindResponse(MaxMindAPI.MINFRAUD fs)
        {
            MemoryStream stream = null;
            TextWriter writer = null;
            try
            {
                stream = new MemoryStream(); // read xml in memory
                writer = new StreamWriter(stream, Encoding.Unicode);
                // get serialise object
                XmlSerializer serializer = new XmlSerializer(typeof(MaxMindAPI.MINFRAUD));
                serializer.Serialize(writer, fs); // read object
                int count = (int)stream.Length; // saves object in memory stream
                byte[] arr = new byte[count];
                stream.Seek(0, SeekOrigin.Begin);
                // copy stream contents in byte array
                stream.Read(arr, 0, count);
                UnicodeEncoding utf = new UnicodeEncoding(); // convert byte array to string
                return utf.GetString(arr).Trim();
            }
            catch
            {
                return string.Empty;
            }
            finally
            {
                if (stream != null)
                {
                    stream.Close();
                }
                if (writer != null)
                {
                    writer.Close();
                }
            }
        }

        // consult MaxMind documentation on Fraud Score Threshold Semantics. 0.0 = lowest risk. 10.0 = highest risk.
        public static Decimal MaxMindFraudCheck(String OrderNumber, Customer ThisCustomer, Address UseBillingAddress, Address UseShippingAddress, out String FraudDetails)
        {
            FraudDetails = String.Empty;
            try
            {
                String BillingEMailDomain = UseBillingAddress.EMail.ToLowerInvariant().Trim();
                if (BillingEMailDomain.Length == 0)
                {
                    BillingEMailDomain = ThisCustomer.EMail.ToLowerInvariant().Trim();
                }
                int i = BillingEMailDomain.IndexOf("@");
                if (i != -1)
                {
                    try
                    {
                        BillingEMailDomain = BillingEMailDomain.Substring(i + 1);
                    }
                    catch { }
                }
                String TXNID = OrderNumber.ToString();
                String SessionID = ThisCustomer.CustomerID.ToString();
                String EMailMD5 = CommonLogic.IIF(UseBillingAddress.EMail.Length != 0, Security.GetMD5Hash(UseBillingAddress.EMail.Trim().ToLowerInvariant()), Security.GetMD5Hash(ThisCustomer.EMail.Trim().ToLowerInvariant()));
                String PasswordMD5 = String.Empty; // we are already hashed, there is no way to provided this to this API call
                String UsernameMD5 = Security.GetMD5Hash(UseBillingAddress.CardName.Trim().ToLowerInvariant());
                String RequestedType = String.Empty; // use highest level service possible
                String binName = String.Empty; // we do not collect this
                String binPhone = String.Empty; // we do not collect this
                String ForwardedIP = CommonLogic.ServerVariables("HTTP_X_FORWARDED_FOR");
                String ThisIP = ThisCustomer.LastIPAddress;
                String RequestIP = CommonLogic.ServerVariables("REMOTE_ADDR");
                if (ThisIP.Length == 0)
                {
                    ThisIP = RequestIP;
                }
                MaxMindAPI.minfraudWebService mmind = new MaxMindAPI.minfraudWebService();
                mmind.Url = AppLogic.AppConfig("MaxMind.SOAPURL");
                MaxMindAPI.MINFRAUD rsp = mmind.minfraud_soap(ThisIP, UseBillingAddress.City, UseBillingAddress.State, UseBillingAddress.PostalCode, UseBillingAddress.Country, BillingEMailDomain, UseBillingAddress.CardNumber.Substring(0, 6), binName, binPhone, UseBillingAddress.Phone, AppLogic.AppConfig("MaxMind.LicenseKey"), RequestedType, ForwardedIP, EMailMD5, UsernameMD5, PasswordMD5, UseShippingAddress.Address1, UseShippingAddress.City, UseShippingAddress.State, UseShippingAddress.PostalCode, UseShippingAddress.Country, TXNID, SessionID);
                FraudDetails = SerializeMaxMindResponse(rsp);
                // clean up the output a bit:
                FraudDetails = FraudDetails.Replace(" xmlns=\"http://www.maxmind.com/maxmind_soap/minfraud_soap\"", "");
                return Localization.ParseUSDecimal(rsp.score);
            }
            catch (Exception ex)
            {
                FraudDetails = ex.Message;
            }
            return -1.0M; // don't let maxmind exception stop the order
        }

        private static void ComputeOrderFreight(InterpriseShoppingCart cart, Customer thisCustomer, SalesOrderDatasetGateway gatewaySalesOrderDataset, SalesOrderFacade facadeSalesOrder)
        {
            // first check if the shipping method is real-time
            string shippingMethodGroup = string.Empty;
            bool useRealTime = false;
            shippingMethodGroup = InterpriseHelper.GetCustomerShippingMethodGroup(thisCustomer, ref useRealTime);

            string shippingMethodCode = cart.FirstItem().m_ShippingMethod;

            if (!string.IsNullOrEmpty(shippingMethodCode))
            {
                gatewaySalesOrderDataset.CustomerSalesOrderView[0].ShippingMethod = shippingMethodCode;
                gatewaySalesOrderDataset.CustomerSalesOrderView[0].ShippingMethodCode = shippingMethodCode;

                if (!cart.HasShippableComponents() ||
                    (InterpriseHelper.IsFreeShippingThresholdEnabled(cart.GetCartSubTotal()) &&
                    InterpriseHelper.ShippingMethodCodeBelongsToFreeShippingMethodList(shippingMethodCode)))
                {
                    gatewaySalesOrderDataset.CustomerSalesOrderView[0].Freight = decimal.Zero;
                    gatewaySalesOrderDataset.CustomerSalesOrderView[0].FreightRate = decimal.Zero;
                    gatewaySalesOrderDataset.CustomerSalesOrderView[0].IsFreightOverwrite = true;
                }
                else
                {
                    decimal handlingExtraFee = 0;
                    decimal exchangeRate = facadeSalesOrder.GetExchangerate(thisCustomer.CurrencyCode);

                    decimal freight = decimal.Zero;
                    decimal freightRate = decimal.Zero;

                    if (useRealTime)
                    {
                        using (SqlConnection con = DB.NewSqlConnection())
                        {
                            con.Open();
                            using (IDataReader reader = DB.GetRSFormat(con, "SELECT Rate FROM EcommerceRealTimeRate with (NOLOCK) WHERE ContactCode = {0} AND ShippingMethodCode = {1}", DB.SQuote(thisCustomer.ContactCode), DB.SQuote(cart.FirstItem().m_ShippingMethod)))
                            {
                                if (reader.Read())
                                {
                                    freight = DB.RSFieldDecimal(reader, "Rate");
                                    freightRate = facadeSalesOrder.ConvertCurrency(exchangeRate, freight, false, string.Empty, Interprise.Framework.Base.Shared.Enum.CurrencyFormat.Total);

                                    gatewaySalesOrderDataset.CustomerSalesOrderView[0].Freight = freight;
                                    gatewaySalesOrderDataset.CustomerSalesOrderView[0].FreightRate = freightRate;
                                    gatewaySalesOrderDataset.CustomerSalesOrderView[0].IsFreightOverwrite = true;
                                }
                            }
                        }
                    }
                    else
                    {
                        facadeSalesOrder.ComputeFreight();

                        if (handlingExtraFee > 0)
                        {
                            // override the display values....
                            freight = gatewaySalesOrderDataset.CustomerSalesOrderView[0].Freight;
                            freight += handlingExtraFee;
                            freightRate = facadeSalesOrder.ConvertCurrency(exchangeRate, freight, false, string.Empty, Interprise.Framework.Base.Shared.Enum.CurrencyFormat.Total);

                            gatewaySalesOrderDataset.CustomerSalesOrderView[0].Freight = freight;
                            gatewaySalesOrderDataset.CustomerSalesOrderView[0].FreightRate = freightRate;
                            gatewaySalesOrderDataset.CustomerSalesOrderView[0].IsFreightOverwrite = true;
                        }
                    }
                }
            }
        }

        public static void ClearCustomerCoupon(string customerCode, bool isCustomerRegistered)
        {
            if (isCustomerRegistered)
            {
                DB.ExecuteSQL("UPDATE Customer SET CouponCode = NULL WHERE CustomerCode = {0}", DB.SQuote(customerCode));
            }
            else
            {
                DB.ExecuteSQL("UPDATE EcommerceCustomer SET CouponCode = NULL WHERE CustomerID = {0}", DB.SQuote(customerCode));
            }
        }

        public static bool IsFreeShippingThresholdEnabled(decimal total)
        {
            decimal threshHold = AppLogic.AppConfigUSDecimal("FreeShippingThreshold");

            return threshHold > decimal.Zero && total >= threshHold;
        }

        public static bool ShippingMethodCodeBelongsToFreeShippingMethodList(string shippingMethodCode)
        {
            string freeShippingMethods = AppLogic.AppConfig("ShippingMethodCodeIfFreeShippingIsOn");

            if (!CommonLogic.IsStringNullOrEmpty(freeShippingMethods))
            {
                foreach (string freeShippingMethod in freeShippingMethods.Split(','))
                {
                    if (freeShippingMethod.Trim().ToUpper().Equals(shippingMethodCode.Trim().ToUpper()))
                    {
                        return true;
                    }
                }
            }

            return false;
        }

        private const string GATEWAY_PROCESSOR_PREFIX = "GATEWAY_";

        private static string GetPreferredGateway()
        {
            return GetPrefferedGatewayInfo()[0];
        }

        private static string[] GetPrefferedGatewayInfo()
        {
            string[] info = new string[] { string.Empty, string.Empty, string.Empty, string.Empty, string.Empty };

            bool hasGatewayDefinedInWebsite = false;

            if (HttpContext.Current == null)
            {
                hasGatewayDefinedInWebsite = false;
            }

            // First try the WebSite if it has one defined            
            using (SqlConnection con = DB.NewSqlConnection())
            {
                con.Open();
                using (IDataReader reader = DB.GetRSFormat(con, "EcommerceGetCreditCardGatewayByWebsite @WebsiteCode = {0}", DB.SQuote(ConfigInstance.WebSiteCode)))
                {
                    hasGatewayDefinedInWebsite = true;

                    info[0] = DB.RSField(reader, "CreditCardGateway");
                    info[1] = DB.RSFieldBool(reader, "IsCustom").ToString();
                    info[2] = string.Empty;
                    info[3] = DB.RSField(reader, "CreditCardGatewayAssemblyName");
                    info[4] = DB.RSField(reader, "MerchantLogin");
                }
            }

            // If we don't have one defined let's try the Customer's Payment Term instead
            if (!hasGatewayDefinedInWebsite)
            {

                using (SqlConnection con = DB.NewSqlConnection())
                {
                    con.Open();
                    using (IDataReader reader = DB.GetRSFormat(con, "eCommerceGetCreditCardGatewayByPaymentTerm @PaymentTermCode = {0}", DB.SQuote(Customer.Current.PaymentTermCode)))
                    {
                        if (reader.Read())
                        {
                            hasGatewayDefinedInWebsite = true;

                            info[0] = DB.RSField(reader, "CreditCardGateway");
                            info[1] = DB.RSFieldBool(reader, "IsCustom").ToString();
                            info[2] = string.Empty;
                            info[3] = DB.RSField(reader, "CreditCardGatewayAssemblyName");
                            info[4] = DB.RSField(reader, "MerchantLogin");
                        }
                    }
                }
            }

            return info;
        }


        private static ICreditCardGatewayInterface GetGatewayProcessorFromCache(string preferredGateway)
        {
            string key = GetGatewayProcessorCacheKey(preferredGateway);
            return HttpContext.Current.Application[key] as ICreditCardGatewayInterface;
        }

        private static string GetGatewayProcessorCacheKey(string preferredGateway)
        {
            return GATEWAY_PROCESSOR_PREFIX + preferredGateway;
        }

        private static void CacheGatewayProcessor(string preferredGateway, ICreditCardGatewayInterface processor)
        {
            string key = GetGatewayProcessorCacheKey(preferredGateway);
            HttpContext.Current.Application[key] = processor;
        }

        private static ICreditCardGatewayInterface GetGatewayProcessorCore()
        {
            string[] info = GetPrefferedGatewayInfo();
            ICreditCardGatewayInterface gatewayProcessor = Interprise.Facade.Base.SimpleFacade.Instance.DeserializeFormSectionPlugin(info, new object[] { }) as ICreditCardGatewayInterface;

            return gatewayProcessor;
        }

        private static void AssignKitItem(SalesOrderDatasetGateway gatewaySalesOrderDataset,
                                            SalesOrderFacade facadeSalesOrder,
                                            Customer customer,
                                            DataRow itemKitRow,
                                            DataRowView lineItemRow,
                                            Guid cartId,
                                            string pricingType)
        {
            ItemKitDatasetGateway kitDataset = new ItemKitDatasetGateway();
            ItemKitFacade kitFacade = new ItemKitFacade(kitDataset);
            kitFacade.ExchangeRate = gatewaySalesOrderDataset.CustomerSalesOrderView[0].ExchangeRate;

            bool isCurrencyIncludedForInventorySelling = InterpriseHelper.IsCurrencyIncludedForInventorySelling(customer.CurrencyCode);

            // Special case 2 
            //  Currency is added in Inventory Selling Currency late
            //  after the kit has been created, ideally the kit should regenerate kit pricing for this currency
            //  but for the meantime, we should handle this by looking into the home currency
            bool currencyIsIncludedInInventorySellingCurrencyButHasNoKitPricingDetailYetForThisItem =
            InterpriseHelper.CurrencyIsIncludedInInventorySellingCurrencyButHasNoKitPricingDetailYet(customer.CurrencyCode, itemKitRow["ItemCode"].ToString());

            string kitDetailQuery =
                string.Format(
                    "exec EcommerceGetWebKitCartDetail @ItemKitCode = {0}, @CurrencyCode = {1}, @LanguageCode = {2}, @CustomerCode = {3}, @CartID = {4}, @ContactCode = {5}",
                    DB.SQuote(itemKitRow["ItemCode"].ToString()),
                    DB.SQuote(CommonLogic.IIF((isCurrencyIncludedForInventorySelling && !currencyIsIncludedInInventorySellingCurrencyButHasNoKitPricingDetailYetForThisItem), customer.CurrencyCode, Currency.GetHomeCurrency())),
                    DB.SQuote(customer.LocaleSetting),
                    DB.SQuote(customer.CustomerCode),
                    DB.SQuote(cartId.ToString()),
                    DB.SQuote(customer.ContactCode)
                );


            using (SqlConnection con = DB.NewSqlConnection())
            {
                con.Open();
                using (IDataReader reader = DB.GetRSFormat(con, kitDetailQuery))
                {
                    while (reader.Read())
                    {
                        ItemKitDatasetGateway.KitConfiguratorRow kitConfigRow;
                        kitConfigRow = kitDataset.KitConfigurator.NewKitConfiguratorRow();

                        kitConfigRow.BeginEdit();
                        kitConfigRow._Select = true;
                        kitConfigRow.ItemName = DB.RSField(reader, "ItemName");
                        kitConfigRow.ItemType = DB.RSField(reader, "ItemType");
                        kitConfigRow.GroupType = DB.RSField(reader, "GroupType");
                        kitConfigRow.ItemCode = DB.RSField(reader, "ItemCode");
                        kitConfigRow.ItemDescription = DB.RSField(reader, "ItemDescription");
                        kitConfigRow.DisplayImage = new byte[] { };
                        kitConfigRow.GroupCode = DB.RSField(reader, "GroupCode");
                        kitConfigRow.Quantity = Convert.ToDecimal(DB.RSFieldDecimal(reader, "Quantity"));
                        kitConfigRow.UnitMeasureCode = DB.RSField(reader, "UnitMeasureCode");
                        kitConfigRow.UnitMeasureQty = Convert.ToInt32(DB.RSFieldDecimal(reader, "UnitMeasureQuantity"));

                        kitConfigRow.SalesPrice = DB.RSFieldDecimal(reader, "Total");
                        kitConfigRow.SalesPriceRate = DB.RSFieldDecimal(reader, "TotalRate");

                        if (isCurrencyIncludedForInventorySelling &&
                            currencyIsIncludedInInventorySellingCurrencyButHasNoKitPricingDetailYetForThisItem)
                        {
                            decimal convertedRate = kitFacade.ConvertCurrency(kitFacade.ExchangeRate, DB.RSFieldDecimal(reader, "Total"), false, customer.CurrencyCode, Interprise.Framework.Base.Shared.Enum.CurrencyFormat.Total);
                            kitConfigRow.SalesPriceRate = convertedRate;
                        }

                        kitConfigRow.ExtSalesPrice = kitConfigRow.SalesPriceRate;
                        kitConfigRow.ExtSalesPriceRate = kitConfigRow.SalesPriceRate;

                        kitConfigRow.SalesPriceDifference = decimal.Zero;
                        kitConfigRow.SalesPriceDifferenceRate = decimal.Zero;

                        if (!kitConfigRow.IsSalesTaxCodeNull() &&
                            !string.IsNullOrEmpty(kitConfigRow.SalesTaxCode))
                        {

                            using (SqlConnection conTax = DB.NewSqlConnection())
                            {
                                con.Open();
                                using (IDataReader taxReader = DB.GetRSFormat(conTax, string.Format("SELECT TaxDescription FROM SystemTaxScheme with (NOLOCK) WHERE TaxCode = {0}",
                                                                                                DB.SQuote(kitConfigRow.SalesTaxCode))))
                                {
                                    if (taxReader.Read())
                                    {
                                        kitConfigRow.TaxDescription =
                                            (taxReader["TaxDescription"] != null &&
                                                taxReader["TaxDescription"] != DBNull.Value &&
                                                string.IsNullOrEmpty((string)taxReader["TaxDescription"])
                                                ) ? (string)taxReader["TaxDescription"] : string.Empty;
                                    }
                                }
                            }
                        }

                        kitConfigRow.EndEdit();

                        kitDataset.KitConfigurator.AddKitConfiguratorRow(kitConfigRow);
                    }
                }
            }

            kitFacade.ComputePercentage();
            kitFacade.ComputeKitItemsSalesPrice(kitFacade.ComputeTotal());

            if (pricingType != Interprise.Framework.Base.Shared.Const.KIT_DISPLAY_ITEM_PRICE)
            {
                pricingType = Interprise.Framework.Base.Shared.Const.KIT_DISPLAY_KIT_PRICE;
            }

            string errMsg = string.Empty;
            facadeSalesOrder.AssignItemKit(
                itemKitRow,
                kitFacade.KitItems,
                lineItemRow,
                Interprise.Framework.Base.Shared.Const.KIT_DISPLAY_KIT_PRICE,
                Convert.ToInt32(lineItemRow["QuantityOrdered"]),
                kitFacade.ComputeTotal(),
                Interprise.Framework.Base.Shared.Enum.TransactionType.SalesOrder,
                ref errMsg,
                string.Empty
            );

            // **************************************************
            //  Explicit Disposal and dereferencing goes here...
            // **************************************************
            kitDataset.Dispose();
            kitFacade.Dispose();
        }


        #endregion

        public static bool IsValidCreditCardNumber(string cardNumber)
        {
            return Interprise.Framework.Base.Shared.Common.IsValidCreditCardNumber(cardNumber);
        }

        public static string GetOrderDetails()
        {
            return "";
        }

        #endregion

        #region Images

        public static string GetGalleryImage(int galleryId, string sizeType, int skinId, string locale)
        {
            string imgPath = CommonLogic.SafeMapPath("images/spacer.gif").Replace("images\\spacer.gif", "images\\gallery\\icon") + "\\"; //+galleryId.ToString();            
            string fileName = string.Empty;

            if (System.IO.File.Exists(imgPath + galleryId.ToString() + ".jpg"))
            {
                fileName = galleryId.ToString() + ".jpg";
            }
            else if (System.IO.File.Exists(imgPath + galleryId.ToString() + ".gif"))
            {
                fileName = galleryId.ToString() + ".gif";
            }
            else if (System.IO.File.Exists(imgPath + galleryId.ToString() + ".png"))
            {
                fileName = galleryId.ToString() + ".png";
            }

            if (!string.IsNullOrEmpty(fileName) &&
                System.IO.File.Exists(imgPath + fileName))
            {
                return "images\\gallery\\icon\\" + fileName;
            }

            return AppLogic.NoPictureImageURL(sizeType == "icon", skinId, locale);
        }

        /// <summary>
        /// Get image from ISSI table.
        /// </summary>
        /// <param name="CounterID">Counter ID.</param>
        /// <param name="sizeType">Type of size to return.</param>
        /// <returns>Image path.</returns>
        public static string GetImage(String CounterID, String sizeType, int skinID, String localeSetting, bool showNoPic)
        {
            string returnImagePath = String.Empty;
            try
            {

                using (SqlConnection con = DB.NewSqlConnection())
                {
                    con.Open();
                    using (IDataReader rs = DB.GetRSFormat(con, "SELECT CustomerURLThumbnail,CustomerURLMedPicture,CustomerURLLargePicture" +
                    " FROM InventoryItem with (NOLOCK) WHERE Counter = " + CounterID))
                    {
                        if (rs.Read())
                        {
                            switch (sizeType)
                            {
                                case "icon":
                                    returnImagePath = DB.RSField(rs, "CustomerURLThumbnail");
                                    break;
                                case "medium":
                                    returnImagePath = DB.RSField(rs, "CustomerURLMedPicture");
                                    break;
                                case "large":
                                    returnImagePath = DB.RSField(rs, "CustomerURLLargePicture");
                                    break;
                                default:
                                    returnImagePath = "";
                                    break;
                            }
                        }
                        else
                        {
                            returnImagePath = "";
                        }
                    }
                }

                return returnImagePath;
            }
            catch { return ""; }
        }

        #endregion

        #region Inventory

        #region GetInventoryItemCode
        public static string GetInventoryItemCode(int counter)
        {
            string ItemCode = string.Empty;

            using (SqlConnection con = DB.NewSqlConnection())
            {
                con.Open();
                using (IDataReader dr = DB.GetRSFormat(con, "SELECT ItemCode FROM InventoryItem with (NOLOCK) WHERE Counter = " + counter))
                {
                    if (dr.Read())
                    {
                        ItemCode = DB.RSField(dr, "ItemCode");
                    }
                }
            }

            return ItemCode;
        }
        public static IEnumerable<KeyValuePair<string, string>> GetInventoryOverideImageList(string itemCodes)
        {
            var lst = new List<KeyValuePair<string, string>>();
            using (var con = DB.NewSqlConnection())
            {
                con.Open();
                using (var reader = DB.GetRSFormat(con, "SELECT ItemCode, Filename FROM InventoryOverrideImage with (NOLOCK) WHERE ItemCode IN ({0}) AND WebSiteCode = {1} AND IsDefaultIcon = 1",
                                                        itemCodes,
                                                        DB.SQuote(InterpriseHelper.ConfigInstance.WebSiteCode)))
                {
                    while (reader.Read())
                    {
                        lst.Add(new KeyValuePair<string, string>(
                                DB.RSField(reader, "ItemCode"),
                                DB.RSField(reader, "Filename")));
                    }
                }
            }
            return lst;
        }
        #endregion

        #region InventoryPricingLevelTable
        public static String InventoryPricingLevelTable(String ItemCode, int SkinID, String CurrencySetting, string pricingLevel)
        {
            String CacheName = "GetQuantityDiscountDisplayTable_" + ItemCode.ToString() + "_" + SkinID.ToString();
            if (AppLogic.CachingOn)
            {
                String CacheData = (String)HttpContext.Current.Cache.Get(CacheName);
                if (CacheData != null)
                {
                    if (CommonLogic.ApplicationBool("DumpSQL"))
                    {
                        HttpContext.Current.Response.Write("Cache Hit Found!<br />");
                    }
                    return CacheData;
                }
            }

            StringBuilder tmpS = new StringBuilder(10000);

            string sql = string.Format("select MinQuantity, MaxQuantity, Discount from inventorypricinglevel with (NOLOCK) where itemcode = {0} and pricinglevel = {1} and currencycode = {2} and unitmeasurecode = {3}",
                DB.SQuote(ItemCode), DB.SQuote(pricingLevel), DB.SQuote(CurrencySetting), DB.SQuote(GetItemDefaultUnitMeasure(ItemCode).Code));


            using (SqlConnection con = DB.NewSqlConnection())
            {
                con.Open();
                using (IDataReader rs = DB.GetRSFormat(con, sql))
                {
                    tmpS.Append("<table border=0 cellpadding=4 cellspacing=0>");
                    tmpS.Append("<tr><td align=center><b>" + AppLogic.GetString("common.cs.10", SkinID, Thread.CurrentThread.CurrentUICulture.Name) +
                        "</b></td><td align=center><b>" + AppLogic.GetString("common.cs.11", SkinID, Thread.CurrentThread.CurrentUICulture.Name) +
                        "</b></td></tr>");
                    while (rs.Read())
                    {
                        tmpS.Append("<tr>");
                        tmpS.Append("<td align=center>");
                        tmpS.Append((int)Convert.ToInt32(DB.RSFieldDecimal(rs, "MinQuantity")) + "-" +
                            ((int)Convert.ToInt32(DB.RSFieldDecimal(rs, "MaxQuantity"))));
                        tmpS.Append("</td>");
                        tmpS.Append("<td align=center>");
                        tmpS.Append(Localization.CurrencyStringForDBWithoutExchangeRate((int)Convert.ToInt32(DB.RSFieldDecimal(rs, "Discount"))) + "%");
                        tmpS.Append("</td>");
                        tmpS.Append("</tr>");
                    }
                    tmpS.Append("</table>");
                }
            }

            if (AppLogic.CachingOn)
            {
                HttpContext.Current.Cache.Insert(CacheName, tmpS.ToString(), null,
                    System.DateTime.Now.AddMinutes(AppLogic.CacheDurationMinutes()), TimeSpan.Zero);
            }
            return tmpS.ToString();
        }
        #endregion

        #region InventoryKitPackagePrice
        public static String InventoryKitPackagePrice(string ItemCode, string CurrencyCode)
        {
            string KitPrice = String.Empty;

            using (var con = DB.NewSqlConnection())
            {
                con.Open();
                using (var dr = DB.GetRSFormat(con, "SELECT DISTINCT(CONVERT(NVARCHAR,SUM(IKPD.TotalRate))) AS TotalRate FROM InventoryKitPricingDetail IKPD with (NOLOCK) INNER JOIN InventoryKitDetail IKD with (NOLOCK) ON ikd.ItemCode = ikpd.ItemCode AND IKPD.ItemKitCode = IKD.ItemKitCode AND ikpd.GroupCode = ikd.GroupCode " +
                                                            " WHERE IKPD.ItemKitCode=" + DB.SQuote(ItemCode) + " AND IKPD.CurrencyCode=" + DB.SQuote(CurrencyCode) +
                                                            " AND IKD.IsDefault = 1 "))
                {
                    if (dr.Read())
                    {
                       KitPrice = DB.RSField(dr, "TotalRate");
                       if (KitPrice.IsNullOrEmptyTrimmed())
                       {
                           return decimal.Zero.ToString();
                       }
                    }
                }
            }

            return KitPrice;
        }

        #endregion

        #region InventoryFreeStock
        public static int InventoryFreeStock(String ItemCode, Customer thisCustomer)
        {
            return Convert.ToInt32(GetInventoryFreeStock(ItemCode, string.Empty, thisCustomer));  
        }
        #endregion



        #region InventoryProductRating
        public static string InventoryProductRating(string ItemCode, int SkinID, string LocaleSetting)
        {
            string rating = string.Empty;
            if (AppLogic.AppConfigBool("Ratings.Enabled"))
            {
                String sql = "EcommerceProductStats " + DB.SQuote(ItemCode);
                Decimal TheAvg = decimal.Zero;

                using (SqlConnection con = DB.NewSqlConnection())
                {
                    con.Open();
                    using (IDataReader rs = DB.GetRSFormat(con, sql))
                    {
                        rs.Read();
                        int NumRatings = DB.RSFieldInt(rs, "NumRatings");
                        int SumRatings = DB.RSFieldInt(rs, "SumRatings");
                        TheAvg = DB.RSFieldDecimal(rs, "AvgRating");
                    }
                }

                if (TheAvg != 0)
                {
                    rating = "<span style=\"vertical-align:bottom;\">" + AppLogic.GetString("ratings.cs.17", SkinID, LocaleSetting) + "</span>&nbsp" + CommonLogic.BuildStarsImage(TheAvg, SkinID);
                }
            }
            return rating;

        }
        #endregion

        #endregion

        #region Customer
        /// <summary>
        /// Update the customer info IsProspect to false if the customer already made an order.
        /// </summary>
        /// <param name="isProspect">Determine if Customer is still a propect or not.</param>
        /// <param name="customerCode">Current CustomerCode.</param>
        public static void SetCustomerProspect(bool isProspect, string customerCode)
        {
            String sql = String.Format("UPDATE Customer SET IsProspect={0} WHERE CustomerCode={1}", Convert.ToByte(isProspect), DB.SQuote(customerCode));
            DB.ExecuteSQL(sql);
        }
        #endregion

        #region Ecommerce
        /// <summary>
        /// Gets all the app configs for the current WebSite.
        /// </summary>
        /// <returns>A <see cref="ApplicationConfigurationDatasetGateway"> containing all the app configs./></returns>
        public static ApplicationConfigurationDatasetGateway SelectAllStoreAppConfigs()
        {

            //Create the facade using a new gateway.
            ApplicationConfigurationDatasetGateway appConfigGateway = new ApplicationConfigurationDatasetGateway();
            ApplicationConfigurationFacade appConfigFacade = new ApplicationConfigurationFacade(appConfigGateway);

            //Build the command set.
            string[][] loadCommandSet = new string[][] { new string[] {
                Interprise.Framework.ECommerce.Const.TableColumns.ECOMMERCEAPPCONFIG_TABLE,
                StoredProcedures.READECOMMERCESTOREAPPCONFIG,
                "@AppConfigGUID", null,
                "@WebsiteCode", InterpriseHelper.ConfigInstance.WebSiteCode}};

            //Load the data into the gateway.
            if (appConfigFacade.LoadDataSet(loadCommandSet,
                Interprise.Framework.Base.Shared.Enum.ClearType.Specific,
                Interprise.Framework.Base.Shared.Enum.ConnectionStringType.Online))
            {
                return appConfigGateway;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// Adds a new "blank" <see cref="ApplicationConfigurationDataset.WebStoreAppConfigRow"/> 
        /// to the passed in <paramref name="appConfigGateway"/>.
        /// </summary>
        /// <param name="appConfigGateway">The <see cref="ApplicationConfigurationDatasetGateway"/> to add the row to.</param>
        /// <returns>The new row that was added to the <paramref name="appConfigGateway"/>.</returns>
        /// <exception cref="ArgumentNullException">Thrown when <paramref name="appConfigGateway"/> is null.</exception>
        public static ApplicationConfigurationDataset.EcommerceAppConfigRow AddNewStoreAppConfigRow(
            ApplicationConfigurationDatasetGateway appConfigGateway)
        {
            //If the appConfigGateway is null then throw an ArgumentNullException.
            if (appConfigGateway == null)
            {
                throw new ArgumentNullException("appConfigGateway");
            }

            //Create the facade using the passed in gateway.
            ApplicationConfigurationFacade appConfigFacade = new ApplicationConfigurationFacade(appConfigGateway);

            //Create a new row for the EcommerceStoreAppConfig table.
            ApplicationConfigurationDatasetGateway.EcommerceAppConfigRow newRow = appConfigGateway.EcommerceAppConfig
                .NewEcommerceAppConfigRow();
            appConfigFacade.AssignAppConfigGUID(newRow);

            //Default some of the fields.
            newRow.BeginEdit();
            newRow.CreatedOn = DateTime.Now;
            newRow.Published = true;
            newRow.WebSiteCode = InterpriseHelper.ConfigInstance.WebSiteCode;
            newRow.SuperOnly = false;
            newRow.EndEdit();

            //Add the row to the EcommerceStoreAppConfig table.
            appConfigGateway.EcommerceAppConfig.AddEcommerceAppConfigRow(newRow);

            //Return the new row.
            return newRow;
        }

        /// <summary>
        /// Saves an app config setting.
        /// </summary>
        /// <param name="appConfigGateway">The records to save.</param>
        /// <returns>True if the records were saved.</returns>
        /// <exception cref="ArgumentNullException">Thrown when <paramref name="appConfigGateway"/> is null.</exception>
        public static bool SaveStoreAppConfigs(ApplicationConfigurationDatasetGateway appConfigGateway)
        {
            //If the appConfigGateway is null then throw an ArgumentNullException.
            if (appConfigGateway == null)
            {
                throw new ArgumentNullException("appConfigGateway");
            }

            //Create the facade using the passed in gateway.
            ApplicationConfigurationFacade appConfigFacade = new ApplicationConfigurationFacade(appConfigGateway);

            //Build the command set.
            string[][] updateCommandSet = new string[][] { new string[] {
                Interprise.Framework.ECommerce.Const.TableColumns.ECOMMERCEAPPCONFIG_TABLE,
                StoredProcedures.CREATEECOMMERCEAPPCONFIG,
                StoredProcedures.UPDATEECOMMERCEAPPCONFIG,
                StoredProcedures.DELETEECOMMERCEAPPCONFIG}};

            //Attempt to update the data in the database.
            return appConfigFacade.UpdateDataSet(updateCommandSet,
                 Interprise.Framework.Base.Shared.Enum.TransactionType.EcommerceAppConfig,
                 string.Empty, false);
        }
        #endregion

        public static string MakeItemLink(string itemCode)
        {
            string link = string.Empty;

            using (var con = DB.NewSqlConnection())
            {
                con.Open();
                using (var reader = DB.GetRSFormat(con, "SELECT Counter, ItemName, ItemDescription FROM EcommerceViewProduct with (NOLOCK) WHERE ItemCode = {0} and ShortString = {1} AND WebSiteCode = {2}", DB.SQuote(itemCode), DB.SQuote(Customer.Current.LocaleSetting), DB.SQuote(InterpriseHelper.ConfigInstance.WebSiteCode)))
                {
                    if (reader.Read())
                    {
                        string seName = DB.RSField(reader, "ItemDescription");
                        if (CommonLogic.IsStringNullOrEmpty(seName))
                        {
                            seName = DB.RSField(reader, "ItemName");
                        }

                        link = SE.MakeProductLink(DB.RSFieldInt(reader, "Counter").ToString(), seName);
                    }
                }
            }

            return link;
        }

        public static string MakeEntityLink(string EntityName, string EntityCode)
        {
            return string.Format("{0}-{1}.aspx", EntityName.Substring(0, 1).ToLowerInvariant(), EntityCode);
        }

        public static string LookUpImageByItemCode(string itemCode, string desizedSize, Customer thisCustomer)
        {
            return LookUpImageByItemCode(itemCode, desizedSize, thisCustomer.SkinID, thisCustomer.LocaleSetting);
        }

        public static string LookUpImageByItemCode(string itemCode, string desizedSize, int skinId, string locale)
        {
            int counter = 0;


            using (SqlConnection con = DB.NewSqlConnection())
            {
                con.Open();
                using (IDataReader reader = DB.GetRSFormat(con, "SELECT Counter FROM InventoryItem with (NOLOCK) WHERE ItemCode = {0}", DB.SQuote(itemCode)))
                {
                    if (reader.Read())
                    {
                        counter = DB.RSFieldInt(reader, "Counter");
                    }
                }
            }

            if (!(counter > 0)) throw new ArgumentException("Item not found!!!", itemCode);

            string imgUrl = string.Empty;
            if (AppLogic.AppConfigBool("Watermark.Enabled"))
            {
                imgUrl = "watermark.axd?e=0&size=" + desizedSize + "&productId=" + counter.ToString();
            }
            else
            {
                imgUrl = AppLogic.LookupImage("Product", counter.ToString(), desizedSize, skinId, locale);
            }

            return imgUrl;
        }

        public static bool IsValidGuid(string id)
        {
            bool valid = false;
            try
            {
                Guid cid = new Guid(id);
                valid = true;
            }
            catch
            {
                valid = false;
            }
            return valid;
        }

        public static void ClearKitItems(Customer thisCustomer, string itemKitCode, Guid cartId)
        {
            // clear the kit items for this customer first...
            string clearKitItemsCommand =
                string.Format("DELETE FROM EcommerceKitCart WHERE CustomerCode = {0} AND ItemKitCode = {1} AND CartID = {2}", DB.SQuote(thisCustomer.CustomerCode), DB.SQuote(itemKitCode), DB.SQuote(cartId.ToString()));

            DB.ExecuteSQL(clearKitItemsCommand);
        }

        public static void ClearKitItems(Customer thisCustomer, string itemKitCode, Guid? cartId, Guid? registryId)
        {
            // clear the kit items for this customer first...
            string clearKitItemsCommand =
                string.Format("DELETE FROM EcommerceKitCart WHERE CustomerCode = {0} AND ItemKitCode = {1} AND CartID = {2}", DB.SQuote(thisCustomer.CustomerCode), DB.SQuote(itemKitCode), DB.SQuote(cartId.ToString()));

            DB.ExecuteSQL(clearKitItemsCommand);
        }

        public static void CreateKitItem(Customer thisCustomer, Guid cartId, string itemKitCode, string itemCode, string groupCode)
        {
            string createKitItemCommand =
            string.Format(
                "INSERT INTO EcommerceKitCart(CartID, ItemKitCode, CustomerCode, ItemCode, GroupCode, CreatedOn) VALUES({0}, {1}, {2}, {3}, {4}, {5})",
                DB.SQuote(cartId.ToString()),
                DB.SQuote(itemKitCode),
                DB.SQuote(thisCustomer.CustomerCode),
                DB.SQuote(itemCode),
                DB.SQuote(groupCode),
                DB.SQuote(Localization.ToDBDateTimeString(DateTime.Now))
            );

            DB.ExecuteSQL(createKitItemCommand);
        }

        public static bool ValidateContactSubscription(Customer thisCustomer)
        {
            bool isEnabled = true;
            DateTime subExpDate = DateTime.Now;
            using (SqlConnection con = DB.NewSqlConnection())
            {
                con.Open();
                using (IDataReader reader = DB.GetRSFormat(con, "SELECT IsEnabled, SubscriptionExpDate FROM EcommerceCustomerActiveSites with (NOLOCK) WHERE ContactCode = {0} AND WebSiteCode = {1}", DB.SQuote(thisCustomer.ContactCode), DB.SQuote(ConfigInstance.WebSiteCode)))
                {
                    if (reader.Read())
                    {
                        isEnabled = DB.RSFieldBool(reader, "IsEnabled");
                        subExpDate = DB.RSFieldDateTime(reader, "SubscriptionExpDate");
                    }
                }
            }

            //Check first if the contact is allowed access on the website
            if (!isEnabled)
            {
                return false;
            }
            else
            {
                //Contact is allowed on the site
                //Check if subscription date is still valid
                if (DateTime.Now > subExpDate)
                {
                    return false;
                }
            }

            //Validation passed
            return true;
        }

        /// <summary>
        /// Generate the contact's valid site data 
        /// </summary>
        /// <param name="thisCustomer"></param>
        public static void CreateContactValidSites(Customer thisCustomer)
        {
            DB.ExecuteSQL(string.Format("exec SetContactMissingSiteInfo @ContactCode = {0}, @WebSiteCode = {1}, @UserCreated = {2}, @Source = {3}", DB.SQuote(thisCustomer.ContactCode), DB.SQuote(ConfigInstance.WebSiteCode), DB.SQuote(ConfigInstance.UserCode), "web"));
        }

        /// <summary>
        /// Stores contact activity on the website.
        /// </summary>
        /// <param name="thisCustomer"></param>
        /// <param name="details"></param>
        public static void CreateContactSiteLog(Customer thisCustomer, string details)
        {
            string createContactSiteLogCommand =
            string.Format(
                "INSERT INTO EcommerceCustomerActiveSitesLog (LogID, ContactCode, WebSiteCode, LogDateTime, LogDetails, UserCreated, DateCreated, UserModified, DateModified) VALUES({0}, {1}, {2}, {3}, {4}, {5}, {6}, {5}, {6})",
                DB.SQuote(Guid.NewGuid().ToString()),
                DB.SQuote(thisCustomer.ContactCode),
                DB.SQuote(ConfigInstance.WebSiteCode),
                DB.SQuote(Localization.ToDBDateTimeString(DateTime.Now)),
                DB.SQuote(details),
                DB.SQuote(ConfigInstance.UserCode),
                DB.SQuote(Localization.ToDBDateTimeString(DateTime.Now))
            );

            DB.ExecuteSQL(createContactSiteLogCommand);

        }

        /// <summary>
        /// Gets whether the currency code is included in InventorySelling
        /// </summary>
        /// <param name="currencyCode"></param>
        /// <returns></returns>
        public static bool IsCurrencyIncludedForInventorySelling(string currencyCode)
        {
            bool isCurrencyIncludedForInventorySelling = false;

            using (SqlConnection con = DB.NewSqlConnection())
            {
                con.Open();
                using (IDataReader reader = DB.GetRSFormat(con, "SELECT CAST(IsIncluded AS BIT) AS IsIncluded FROM InventorySellingCurrency with (NOLOCK) WHERE CurrencyCode = {0}", DB.SQuote(currencyCode)))
                {
                    isCurrencyIncludedForInventorySelling = reader.Read() && DB.RSFieldBool(reader, "IsIncluded");
                }
            }

            return isCurrencyIncludedForInventorySelling;
        }

        public static bool CurrencyIsIncludedInInventorySellingCurrencyButHasNoKitPricingDetailYet(string currencyCode, string itemCode)
        {
            bool currencyIsIncludedInInventorySellingCurrencyButHasNoKitPricingDetailYetForThisItem = false;
            currencyIsIncludedInInventorySellingCurrencyButHasNoKitPricingDetailYetForThisItem =
            DB.GetSqlN(string.Format("SELECT COUNT(*) AS N FROM InventoryKitPricingDetail with (NOLOCK) WHERE Currencycode = {0} and ItemKitCode = {1}", DB.SQuote(currencyCode), DB.SQuote(itemCode))) == 0;

            return currencyIsIncludedInInventorySellingCurrencyButHasNoKitPricingDetailYetForThisItem;
        }

        public static decimal CheckAvailableStock(Customer thisCustomer, string itemcode, string unitMeasure)
        {
            decimal numberofstock = 0.0M;
            string checkAvailableStockQuery =
            string.Format(
                "SELECT SUM(FreeStock) FROM AvailabilityView with (NOLOCK) WHERE ItemCode = {0} AND WarehouseCode = {1} AND UM = {2}",
                DB.SQuote(itemcode),
                AppLogic.AppConfigBool("ShowInventoryFromAllWarehouses") ? "WarehouseCode" : DB.SQuote(thisCustomer.WarehouseCode),
                DB.SQuote(unitMeasure)
            );


            using (SqlConnection con = DB.NewSqlConnection())
            {
                con.Open();
                using (IDataReader reader = DB.GetRSFormat(con, checkAvailableStockQuery))
                {
                    if (reader.Read())
                    {
                        numberofstock = DB.RSFieldDecimal(reader, "FreeStock");
                    }
                }
            }

            return numberofstock;
        }

        public static bool HasAvailableStock(Customer thisCustomer, string itemcode, string unitMeasure)
        {
            bool hasStock = false;
            string checkAvailableStockQuery =
            string.Format(
                "SELECT SUM(FreeStock) FROM AvailabilityView with (NOLOCK) WHERE ItemCode = {0} AND WarehouseCode = {1} AND UM = {2}",
                DB.SQuote(itemcode),
                AppLogic.AppConfigBool("ShowInventoryFromAllWarehouses") ? "WarehouseCode" : DB.SQuote(thisCustomer.WarehouseCode),
                DB.SQuote(unitMeasure)
            );

            using (SqlConnection con = DB.NewSqlConnection())
            {
                con.Open();
                using (IDataReader reader = DB.GetRSFormat(con, checkAvailableStockQuery))
                {
                    hasStock = reader.Read() && DB.RSFieldDecimal(reader, "FreeStock") > decimal.Zero;
                }
            }

            return hasStock;
        }

        public static UnitMeasureInfo GetItemDefaultUnitMeasure(string itemCode)
        {
            return GetItemUnitMeasure(itemCode, string.Empty);
        }

        public static UnitMeasureInfo GetItemUnitMeasure(string itemCode, string unitMeasureCode)
        {
            var info = new UnitMeasureInfo();

            bool useBaseUnitMeasure = string.IsNullOrEmpty(unitMeasureCode);
            string query = string.Empty;
            if (useBaseUnitMeasure)
            {
                query = string.Format("SELECT UnitMeasureCode, UnitMeasureQty FROM InventoryUnitMeasure with (NOLOCK) WHERE ItemCode = {0} AND  IsBase = 1", DB.SQuote(itemCode));
            }
            else
            {
                query = string.Format("SELECT UnitMeasureCode, UnitMeasureQty FROM InventoryUnitMeasure with (NOLOCK) WHERE ItemCode = {0} AND  UnitMeasureCode = {1}", DB.SQuote(itemCode), DB.SQuote(unitMeasureCode));
            }

            using (var con = DB.NewSqlConnection())
            {
                con.Open();
                using (var reader = DB.GetRSFormat(con, query))
                {
                    if (reader.Read())
                    {
                        info.Code = DB.RSField(reader, "UnitMeasureCode");
                        info.Quantity = DB.RSFieldDecimal(reader, "UnitMeasureQty");
                    }
                }
            }

            return info;
        }

        public static List<UnitMeasureInfo> GetListItemUnitMeasure(string itemCode, IEnumerable<string> unitMeasureCodes)
        {
            var lstUnitMeasureList = new List<UnitMeasureInfo>();
            bool useBaseUnitMeasure = (unitMeasureCodes == null);
            string codesQuery = string.Empty;

            if (!useBaseUnitMeasure)
            {
                codesQuery = string.Join(",", unitMeasureCodes.Select(u => DB.SQuote(u)));
                useBaseUnitMeasure = false;
            }

            string query = string.Empty;
            if (useBaseUnitMeasure)
            {
                query = string.Format("SELECT UnitMeasureCode, UnitMeasureQty FROM InventoryUnitMeasure with (NOLOCK) WHERE ItemCode = {0} AND IsBase = 1", DB.SQuote(itemCode));
            }
            else
            {
                query = string.Format("SELECT UnitMeasureCode, UnitMeasureQty FROM InventoryUnitMeasure with (NOLOCK) WHERE ItemCode = {0} AND UnitMeasureCode IN ({1})", DB.SQuote(itemCode), codesQuery);
            }

            using (var con = DB.NewSqlConnection())
            {
                con.Open();
                using (var reader = DB.GetRSFormat(con, query))
                {
                    while (reader.Read())
                    {
                        var info = new UnitMeasureInfo()
                        {
                            Code = DB.RSField(reader, "UnitMeasureCode"),
                            Quantity = DB.RSFieldDecimal(reader, "UnitMeasureQty")
                        };
                        lstUnitMeasureList.Add(info);
                    }
                }
            }

            return lstUnitMeasureList;
        }

        internal struct PriceListData
        {
            internal string PriceLevelCode;
            internal Dictionary<string, PriceListUnitMeasureData> UnitMeasures;

            internal PriceListData(string priceLevelCode)
            {
                PriceLevelCode = priceLevelCode;
                UnitMeasures = new Dictionary<string, PriceListUnitMeasureData>();
            }
        }

        internal struct PriceListUnitMeasureData
        {
            internal string UnitMeasureCode;
            internal string UnitMeasureDescription;
            internal decimal UnitMeasureQuantity;
            internal List<PriceListQuantityRangeData> QuantityRanges;

            internal PriceListUnitMeasureData(string unitMeasureCode, string unitMeasureDescription, decimal unitMeasureQuantity)
            {
                this.UnitMeasureCode = unitMeasureCode;
                this.UnitMeasureDescription = unitMeasureDescription;
                this.UnitMeasureQuantity = unitMeasureQuantity;
                this.QuantityRanges = new List<PriceListQuantityRangeData>();
            }
        }

        internal struct PriceListQuantityRangeData
        {
            internal decimal MinQuantity;
            internal decimal MaxQuantity;
            internal decimal Discount;
            internal decimal UnitSellingPrice;
        }

        public static string GetInventoryPricingLevelTable(Customer thisCustomer, string itemCode, out bool hasPricingLevel)
        {
            var priceList = new PriceListData(thisCustomer.PricingLevel);

            int quantityDecimalPlaces = GetInventoryDecimalPlacesPreference();

            hasPricingLevel = false;

            string customerCode = CommonLogic.IIF(thisCustomer.IsNotRegistered, thisCustomer.AnonymousCustomerCode, thisCustomer.CustomerCode);
            // Get the pricing table discounts...

            using (var con = DB.NewSqlConnection())
            {
                string unitMeasureCode = string.Empty;
                string unitMeasureDescription = string.Empty;
                decimal unitMeasureQuantity;
                string currencyCode = string.Empty;
                decimal minQuantity;
                decimal maxQuantity;
                decimal discount;
                decimal unitSellingPrice;

                con.Open();
                using (var reader = DB.GetRSFormat(con, "exec eCommerceGetItemPricingLevel @ItemCode = {0}, @CustomerCode = {1}", DB.SQuote(itemCode), DB.SQuote(customerCode)))
                {
                    while (reader.Read())
                    {
                        hasPricingLevel = true;

                        unitMeasureCode = DB.RSField(reader, "UnitMeasureCode");
                        unitMeasureDescription = DB.RSField(reader, "UnitMeasureDescription");
                        unitMeasureQuantity = DB.RSFieldDecimal(reader, "UnitMeasureQty");
                        currencyCode = DB.RSField(reader, "CurrencyCode");
                        minQuantity = DB.RSFieldDecimal(reader, "MinQuantity");
                        maxQuantity = DB.RSFieldDecimal(reader, "MaxQuantity");
                        discount = DB.RSFieldDecimal(reader, "Discount");
                        unitSellingPrice = DB.RSFieldDecimal(reader, "SalesPrice");

                        PriceListUnitMeasureData unitMeasure;

                        if (!priceList.UnitMeasures.ContainsKey(unitMeasureCode))
                        {
                            priceList.UnitMeasures.Add(unitMeasureCode, new PriceListUnitMeasureData(unitMeasureCode, unitMeasureDescription, unitMeasureQuantity));
                        }

                        unitMeasure = priceList.UnitMeasures[unitMeasureCode];

                        var quantityRange = new PriceListQuantityRangeData()
                        {
                            MinQuantity = minQuantity,
                            MaxQuantity = maxQuantity,
                            Discount = discount,
                            UnitSellingPrice = unitSellingPrice
                        };

                        unitMeasure.QuantityRanges.Add(quantityRange);
                    }
                }
            }

            // render the html
            var output = new StringBuilder();

            if (priceList.UnitMeasures.Count > 0)
            {
                // table begin tag
                output.Append("<table border=0 cellpadding=4 cellspacing=0 >");

                // the headers -----------------------------------------------------
                output.Append("<tr>");
                output.Append("    <td align=center>");
                // Quantity Header
                output.AppendFormat("        <b>{0}</b>", AppLogic.GetString("common.cs.10", thisCustomer.SkinID, thisCustomer.LocaleSetting));
                output.Append("    </td>");
                output.Append("    <td align=center>");
                // Discount Header
                output.AppendFormat("        <b>{0}</b>", AppLogic.GetString("common.cs.11", thisCustomer.SkinID, thisCustomer.LocaleSetting));
                output.Append("    </td>");
                output.Append("    <td align=center>");
                // Unit Selling Price header
                output.AppendFormat("        <b>{0}</b>", AppLogic.GetString("common.cs.25", thisCustomer.SkinID, thisCustomer.LocaleSetting));
                output.Append("    </td>");
                output.Append("</tr>");

                // render the child items..
                foreach (PriceListUnitMeasureData unitMeasure in priceList.UnitMeasures.Values)
                {
                    // the Unit Measure row Header
                    output.Append("<tr>");
                    output.Append("    <td align=left colspan=3 >");
                    output.AppendFormat("        <b>{0}</b>", HttpUtility.HtmlEncode(unitMeasure.UnitMeasureDescription));
                    output.Append("    </td>");
                    output.Append("</tr>");

                    for (int ctr = 0; ctr < unitMeasure.QuantityRanges.Count; ctr++)
                    {
                        var quantityRange = unitMeasure.QuantityRanges[ctr];

                        output.Append("<tr>");

                        // the quanty range column
                        output.Append("    <td align=left >");
                        if (ctr + 1 == unitMeasure.QuantityRanges.Count)
                        {
                            output.AppendFormat("        {0} {1}", Localization.ParseLocaleDecimal(quantityRange.MinQuantity, Customer.Current.LocaleSetting), AppLogic.GetString("showproduct.aspx.44", thisCustomer.SkinID, thisCustomer.LocaleSetting));
                        }
                        else
                        {
                            output.AppendFormat("        {0} - {1}", Localization.ParseLocaleDecimal(quantityRange.MinQuantity, Customer.Current.LocaleSetting), Localization.ParseLocaleDecimal(quantityRange.MaxQuantity, Customer.Current.LocaleSetting));
                        }
                        output.Append("    </td>");

                        // Discount
                        output.Append("    <td align=right >");
                        output.AppendFormat("        {0} %", Localization.ParseLocaleDecimal(quantityRange.Discount, Customer.Current.LocaleSetting));
                        output.Append("    </td>");

                        // Unit Selling Price
                        output.Append("    <td align=right >");

                        decimal price = quantityRange.UnitSellingPrice;

                        if (thisCustomer.VATSettingReconciled == VatDefaultSetting.Inclusive)
                        {
                            decimal priceRate, cost, costRate, vat;
                            priceRate = price;
                            string currencyCode = string.Empty;
                            bool byTotalQuantity = false;
                            decimal x_salesPrice = decimal.Zero;
                            bool isSalesPriceInBasecurrency = false;
                            decimal regularPrice = decimal.Zero;
                            decimal promotionalPrice = decimal.Zero;
                            string pricing = string.Empty;
                            decimal percent = decimal.Zero;
                            decimal discount = decimal.Zero;
                            decimal categoryDiscount = decimal.Zero;
                            string customerItemCode = string.Empty;
                            string customerItemDescription = string.Empty;
                            string inventoryItemDescription = string.Empty;
                            decimal basePricingCost = decimal.Zero;
                            decimal baseAverageCost = decimal.Zero;
                            bool isInventorySpecialPriceExpired = false;
                            bool isCustomerSpecialPriceExpired = false;

                            BaseSalesOrderFacade.GetPrice(
                                    string.Empty,
                                    ref currencyCode,
                                    itemCode,
                                    unitMeasure.UnitMeasureCode,
                                    unitMeasure.UnitMeasureQuantity,
                                    DateTime.Today,
                                    decimal.Zero,
                                    decimal.Zero,
                                    Interprise.Framework.Base.Shared.Enum.DefaultPricing.None,
                                    Interprise.Framework.Base.Shared.Enum.PricingMethod.None,
                                    string.Empty,
                                    ref byTotalQuantity,
                                    ref x_salesPrice,
                                    ref isSalesPriceInBasecurrency,
                                    ref regularPrice,
                                    ref promotionalPrice,
                                    ref pricing,
                                    ref percent,
                                    ref discount,
                                    ref categoryDiscount,
                                    ref customerItemCode,
                                    ref customerItemDescription,
                                    ref inventoryItemDescription,
                                    ref basePricingCost,
                                    ref baseAverageCost,
                                    ref isInventorySpecialPriceExpired,
                                    ref isCustomerSpecialPriceExpired);

                            cost = baseAverageCost;
                            decimal exchangeRate = SimpleFacade.Instance.GetExchangerate(thisCustomer.CurrencyCode);
                            costRate = SimpleFacade.Instance.ConvertCurrency(exchangeRate, cost, false, thisCustomer.CurrencyCode, Interprise.Framework.Base.Shared.Enum.CurrencyFormat.Total);

                            if (thisCustomer.IsNotRegistered)
                            {
                                customerCode = thisCustomer.AnonymousCustomerCode;
                            }

                            vat = ItemTaxFacade.CalculateTax(customerCode,
                                    itemCode,
                                    unitMeasure.UnitMeasureCode,
                                    price,
                                    priceRate,
                                    cost,
                                    costRate,
                                    decimal.One);

                            if (thisCustomer.VATSettingReconciled == VatDefaultSetting.Inclusive)
                            {
                                price += vat;
                            }
                        }

                        output.AppendFormat("        {0}", FormatCurrencyForCustomer(price, thisCustomer.CurrencyCode));

                        if (AppLogic.AppConfigBool("VAT.Enabled"))
                        {
                            if (thisCustomer.VATSettingReconciled == VatDefaultSetting.Inclusive)
                            {
                                output.Append(" <span class=\"VATLabel\">" + AppLogic.GetString("showproduct.aspx.38", thisCustomer.SkinID, thisCustomer.LocaleSetting) + "</span>\n");
                            }
                            else
                            {
                                output.Append(" <span class=\"VATLabel\">" + AppLogic.GetString("showproduct.aspx.37", thisCustomer.SkinID, thisCustomer.LocaleSetting) + "</span>\n");
                            }
                        }
                        output.Append("    </td>");

                        output.Append("</tr>");
                    }
                }

                // table end tag
                output.Append("</table>");
            }

            return output.ToString();
        }

        public static int GetInventoryDecimalPlacesPreference()
        {
            int quantityDecimalPlaces = 0;

            quantityDecimalPlaces = AppLogic.InventoryDecimalPlacesPreference;

            if (quantityDecimalPlaces < 0) quantityDecimalPlaces = 2;
            return quantityDecimalPlaces;
        }

        public static string GetUserUnitMeasureSystem(string userCode)
        {
            string unitMeasureSystem = string.Empty;

            using (SqlConnection con = DB.NewSqlConnection())
            {
                con.Open();
                using (IDataReader reader = DB.GetRSFormat(con, "select unitmeasuresystem from systemuserpreference with (NOLOCK) where usercode=" + DB.SQuote(userCode)))
                {
                    if (reader.Read())
                    {
                        unitMeasureSystem = DB.RSField(reader, "unitmeasuresystem");
                    }

                }
            }

            return unitMeasureSystem;
        }

        public static string GetInventoryUnitMeasure(string localeSetting, int decimalPlacesPreferences, string unitMeasureValue)
        {
            NumberFormatInfo formatter = (new CultureInfo(localeSetting)).NumberFormat;
            formatter.NumberDecimalDigits = decimalPlacesPreferences;
            formatter.PercentDecimalDigits = decimalPlacesPreferences;
            decimal measureValue = Convert.ToDecimal(unitMeasureValue);

            return measureValue.ToString("N", formatter);
        }

        public static String ShowInventorySubstituteOptions(String itemCode, int showNum, bool showPics, String teaser, Customer thisCustomer)
        {

            String RelatedProductList = String.Empty;
            string ImgFilename = string.Empty;
            bool existing = false;
            bool exists = false;

            StringBuilder tmpS = new StringBuilder(10000);
            string displayFormat = AppLogic.AppConfig("SubstituteProductsFormat");

            // NOTE:
            //  Because of the Cache API uses unique keys cache entries
            //  We SHOULD LEAVE OUT the CurrentDate parameters since DateTime.Now
            //  is NON-Deterministic, it will return a new unique value EVERYTIME it's called
            //  Hence adding it to our cache key would work out against us since we'll be adding
            //  a NEW cache entry EVERYTIME.
            string query = string.Format("exec GetEcommerceSubstituteItems @ItemCode = {0}, @WebSiteCode = {1}, @ContactCode = {2}, @CurrentDate = {3}, @ProductFilterID = {4}",
                            DB.SQuote(itemCode),
                            DB.SQuote(ConfigInstance.WebSiteCode),
                            DB.SQuote(thisCustomer.ContactCode),
                            DB.SQuote(Localization.DateTimeStringForDB(DateTime.Now)), DB.SQuote(thisCustomer.ProductFilterID));

            DataSet ds = DB.GetDS(query, false);

            if (ds.Tables[0].Rows.Count > 0)
            {
                tmpS.Append("<table width=\"100%\" cellpadding=\"2\" cellspacing=\"0\" border=\"0\" style=\"border-style: solid; border-width: 0px; border-color: #" + AppLogic.AppConfig("HeaderBGColor") + "\">\n");
                tmpS.Append("<tr><td align=\"left\" valign=\"top\">\n");
                tmpS.Append(" <span class=\"UpsellSectionLabel\"> " + AppLogic.GetString("showproduct.aspx.50", thisCustomer.SkinID, thisCustomer.LocaleSetting) + " </span>");
                tmpS.Append("<table width=\"100%\" cellpadding=\"4\" cellspacing=\"0\" border=\"0\" style=\"" + AppLogic.AppConfig("BoxFrameStyle") + "\">\n");
                tmpS.Append("<tr><td align=\"left\" valign=\"top\" class=\"RelatedProductsBoxStyle\">\n");

                if (teaser.Length != 0)
                {
                    tmpS.Append("<p><b>" + teaser + "</b></p>\n");
                }

                try
                {
                    bool empty = (ds.Tables[0].Rows.Count > 0);
                    switch (displayFormat.ToUpperInvariant())
                    {
                        case "GRID":
                            // GRID FORMAT:
                            int ItemNumber = 1;
                            int ItemsPerRow = AppLogic.AppConfigUSInt("SubstituteGridColWidth");
                            if (ItemsPerRow == 0)
                            {
                                ItemsPerRow = 4;
                            }
                            tmpS.Append("<table border=\"0\" cellpadding=\"0\" cellspacing=\"4\" width=\"100%\">");
                            foreach (DataRow row in ds.Tables[0].Rows)
                            {
                                ImgFilename = "";
                                string displayName = DB.RowField(row, "ItemDescription");
                                if (CommonLogic.IsStringNullOrEmpty(displayName))
                                {
                                    displayName = DB.RowField(row, "ItemName");
                                }

                                if (ItemNumber == 1)
                                {
                                    tmpS.Append("<tr>");
                                }
                                if (ItemNumber == ItemsPerRow + 1)
                                {
                                    tmpS.Append("</tr><tr><td colspan=\"" + ItemsPerRow.ToString() + "\" height=\"8\"></td></tr>");
                                    ItemNumber = 1;
                                }
                                tmpS.Append("<td width=\"" + (100 / ItemsPerRow).ToString() + "%\" height=\"150\" align=\"center\" valign=\"top\">");
                                
                                if (showPics)
                                {
                                    String ImgUrl = String.Empty;
                                    XSLTExtensionBase xslt = new XSLTExtensionBase(thisCustomer, thisCustomer.SkinID);
                                    ImgUrl = xslt.DisplayImage("Product", DB.RowField(row, "Counter"), "icon");

                                    if (ImgUrl.Length != 0)
                                    {
                                        tmpS.Append("<a href=\"" + SE.MakeProductLink(DB.RowFieldInt(row, "Counter").ToString(), displayName) + "\">");
                                        tmpS.Append(ImgUrl);
                                        tmpS.Append("</a>");
                                        tmpS.Append("<br /><br />");
                                    }
                                }
                                tmpS.Append("<a title=\"" + displayName + "\" href=\"" + SE.MakeProductLink(DB.RowFieldInt(row, "Counter").ToString(), CommonLogic.Ellipses(displayName, 20, false)) + "\">");
                                tmpS.Append(HttpUtility.HtmlEncode(CommonLogic.Ellipses(displayName, 20, false)) + "</a>");
                                tmpS.Append("</td>");
                                ItemNumber++;
                            }
                            for (int ctr = ItemNumber; ctr <= ItemsPerRow; ctr++)
                            {
                                tmpS.Append("<td>&nbsp;</td>");
                            }
                            tmpS.Append("</tr>");
                            tmpS.Append("</table>");
                            break;

                        case "TABLE":
                            tmpS.Append("<table width=\"100%\" cellpadding=\"2\" cellspacing=\"0\" border=\"0\">\n");
                            int i = 1;
                            foreach (DataRow row in ds.Tables[0].Rows)
                            {
                                string displayName = DB.RowField(row, "ItemDescription");
                                string extendedDescription = DB.RowField(row, "ExtendedDescription");

                                if (CommonLogic.IsStringNullOrEmpty(displayName))
                                {
                                    displayName = DB.RowField(row, "ItemName");
                                }

                                if (i > showNum)
                                {
                                    tmpS.Append("<tr><td " + CommonLogic.IIF(showPics, "colspan=\"2\"", "") + "><hr size=\"1\" class=\"LightCellText\"/></td></tr>");
                                    break;
                                }
                                if (i > 1)
                                {
                                    tmpS.Append("<tr><td " + CommonLogic.IIF(showPics, "colspan=\"2\"", "") + "><hr size=\"1\" class=\"LightCellText\"/></td></tr>");
                                }
                                tmpS.Append("<tr>");
                                String ImgUrl = String.Empty;
                                string subItemCode = InterpriseHelper.GetInventoryItemCode(DB.RowFieldInt(row, "Counter"));
                                using (SqlConnection con = DB.NewSqlConnection())
                                {
                                    con.Open();
                                    using (IDataReader reader = DB.GetRSFormat(con, "SELECT Filename FROM InventoryOverrideImage with (NOLOCK) WHERE ItemCode = {0} AND WebSiteCode = {1} AND IsDefaultIcon = 1", DB.SQuote(subItemCode), DB.SQuote(InterpriseHelper.ConfigInstance.WebSiteCode)))
                                    {
                                        existing = reader.Read();
                                        if (existing)
                                        {
                                            ImgFilename = (DB.RSField(reader, "Filename"));
                                        }
                                    }
                                }

                                ImgUrl = AppLogic.LocateImageFilenameUrl("Product", subItemCode, "icon", ImgFilename, AppLogic.AppConfigBool("Watermark.Enabled"), out exists);

                                if (showPics)
                                {
                                    string seTitle = "";
                                    string seAltText = "";
                                    AppLogic.GetSEImageAttributes(subItemCode, "icon", thisCustomer.LanguageCode, ref seTitle, ref seAltText);

                                    tmpS.Append("<td align=\"left\" valign=\"top\">\n");
                                    tmpS.Append("<a href=\"" + SE.MakeProductLink(DB.RowFieldInt(row, "Counter").ToString(), displayName) + "\">");
                                    tmpS.Append("<img align=\"left\" src=\"" + ImgUrl + "\" border=\"0\" alt=\"" + seAltText + "\" title=\"" + seTitle + "\" />");
                                    tmpS.Append("</a>");
                                    tmpS.Append("</td>");
                                }

                                tmpS.Append("<td align=\"left\" valign=\"top\">\n");
                                tmpS.Append("<b class=\"a4\">");
                                tmpS.Append("<a title=\"" + displayName + "\" href=\"" + SE.MakeProductLink(DB.RowFieldInt(row, "Counter").ToString(), CommonLogic.Ellipses(displayName, 20, false)) + "\">");
                                tmpS.Append(AppLogic.MakeProperObjectName(DB.RowFieldInt(row, "Counter").ToString(), HttpUtility.HtmlEncode(CommonLogic.Ellipses(displayName, 20, false)), thisCustomer.LocaleSetting));
                                tmpS.Append("</a></b><br />\n");
                                if (DB.RowField(row, "WebDescription").Length != 0)
                                {
                                    String tmpD = DB.RowField(row, "WebDescription");
                                    if (AppLogic.ReplaceImageURLFromAssetMgr)
                                    {
                                        tmpD = tmpD.Replace("../images", "images");
                                    }
                                    tmpS.Append("<span class=\"a2\">" + tmpD + "</span><br />\n");
                                }
                                else
                                {
                                    tmpS.Append("<span class=\"a2\">" + HttpUtility.HtmlEncode(extendedDescription) + "</span><br />\n");

                                }

                                tmpS.Append("<div class=\"a1\" style=\"PADDING-BOTTOM: 10px; PADDING-TOP: 10px;\">\n");
                                tmpS.Append("<a href=\"" + SE.MakeProductLink(DB.RowFieldInt(row, "Counter").ToString(), displayName) + "\">");
                                tmpS.Append(AppLogic.GetString("common.cs.9", thisCustomer.SkinID, Thread.CurrentThread.CurrentUICulture.Name));
                                tmpS.Append("</a>");
                                tmpS.Append("</div>\n");
                                tmpS.Append("</td>");
                                tmpS.Append("</tr>");
                                i++;
                            }
                            tmpS.Append("</table>\n");
                            break;
                    }
                }
                catch
                {
                    // people put all kinds of crap in relatedproducts field, so have to trap those errors, or the site fails.
                }

                tmpS.Append("</td></tr>\n");
                tmpS.Append("</table>\n");
                tmpS.Append("</td></tr>\n");
                tmpS.Append("</table>\n");
            }
            ds.Dispose();
            return tmpS.ToString();
        }

        public static void ClearCart(ShoppingCart cart)
        {
            ClearCart(cart.CartType, cart.ThisCustomer.CustomerCode, cart.ThisCustomer.ContactCode);
        }

        public static void ClearCart(CartTypeEnum cartType, string customerCode, string contactCode)
        {
            // clear per line item
            DB.ExecuteSQL(string.Format("exec EcommerceClearCart @CartType = {0}, @CustomerCode = {1}, @ContactCode = {2}", (int)cartType, DB.SQuote(customerCode), DB.SQuote(contactCode)));
        }

        public static void UpdateAnonymousCart(CartTypeEnum cartType, string anonymousCode, string customerCode, string shiptocode, string contactCode)
        {
            DB.ExecuteSQL("exec EcommerceUpdateCart @CartType = {0}, @CustomerCode = {1}, @AnonymousCode = {2}, @DefaultShiptoCode = {3}, @ContactCode= {4}", (int)cartType, DB.SQuote(customerCode), DB.SQuote(anonymousCode), DB.SQuote(shiptocode), DB.SQuote(contactCode));
        }

        public static bool ReOrderToCart(string salesOrderCode, Customer thisCustomer, Dictionary<string, EntityHelper> entityHelpers, ref string status)
        {
            bool hasRecord = false;


            using (SqlConnection con = DB.NewSqlConnection())
            {
                con.Open();
                using (IDataReader reader = DB.GetRSFormat(con, "exec eCommerceReorder @SalesOrderCode = {0}", DB.SQuote(salesOrderCode)))
                {
                    hasRecord = reader.Read();

                    if (hasRecord)
                    {
                        if (AppLogic.AppConfigBool("Reorder.ClearCartBeforeAddingReorderItems"))
                        {
                            ClearCart(CartTypeEnum.ShoppingCart, thisCustomer.CustomerCode, thisCustomer.ContactCode);
                        }
                    }
                    else
                    {
                        status = AppLogic.GetString("reorder.aspx.3", thisCustomer.SkinID, thisCustomer.LocaleSetting);
                        return false;
                    }

                    ShoppingCart cart = new ShoppingCart(entityHelpers, thisCustomer.SkinID, thisCustomer, CartTypeEnum.ShoppingCart, string.Empty, false, true);

                    while (hasRecord)
                    {
                        int itemCounter = DB.RSFieldInt(reader, "Counter");
                        string itemCode = DB.RSField(reader, "ItemCode");
                        string itemType = DB.RSField(reader, "ItemType");
                        string unitMeasureCode = DB.RSField(reader, "UnitMeasureCode");
                        decimal quantityOrdered = DB.RSFieldDecimal(reader, "QuantityOrdered");
                        int linenum = DB.RSFieldInt(reader, "LineNum");
                        Guid cartId = Guid.Empty;

                        if (itemType == Interprise.Framework.Base.Shared.Const.ITEM_TYPE_KIT)
                        {
                            KitComposition compositionInOrder = KitComposition.FromSalesOrder(thisCustomer, salesOrderCode, itemCode, linenum);
                            cartId = cart.AddItem(thisCustomer, thisCustomer.PrimaryShippingAddressID, itemCode, itemCounter, Convert.ToInt32(quantityOrdered), unitMeasureCode, CartTypeEnum.ShoppingCart, compositionInOrder);
                        }
                        else
                        {
                            cartId = cart.AddItem(thisCustomer, thisCustomer.PrimaryShippingAddressID, itemCode, itemCounter, Convert.ToInt32(quantityOrdered), unitMeasureCode, CartTypeEnum.ShoppingCart);
                        }
                        hasRecord = reader.Read();
                    }
                }
            }

            return true;
        }

        public static decimal GetItemWeight(string itemCode, string unitMeasureCode)
        {
            decimal weight = decimal.Zero;


            using (SqlConnection con = DB.NewSqlConnection())
            {
                con.Open();
                using (IDataReader reader = DB.GetRSFormat(con, string.Format("SELECT WeightInPounds FROM InventoryUnitMeasure with (NOLOCK) WHERE ItemCode = {0} AND UnitMeasureCode = {1}", DB.SQuote(itemCode), DB.SQuote(unitMeasureCode))))
                {
                    if (reader.Read())
                    {
                        // NOTE :
                        //  Since the weight in the Unit Measure
                        //  is always converted whether it's inputted in pounds or kilograms
                        //  we can be sure that the value we have here is always converted.
                        weight = DB.RSFieldDecimal(reader, "WeightInPounds");
                    }
                }
            }

            return weight;
        }

        public static string GetCustomerShippingMethodGroup(Customer thisCustomer, ref bool isRealTime)
        {
            string shippingMethodGroup = string.Empty;

            string basedOnCustomerCode = thisCustomer.CustomerCode;
            if (thisCustomer.IsNotRegistered)
            {
                basedOnCustomerCode = thisCustomer.AnonymousCustomerCode;
            }

            // NOTE :
            //  We will not depend on the shipping method of the customer ship to
            //  If it has one defined and the only shipping method in the group is RealTime
            //  Then we will use Real-Time shipping. 

            using (SqlConnection con = DB.NewSqlConnection())
            {
                con.Open();
                using (IDataReader reader = DB.GetRSFormat(con, "SELECT cs.ShippingMethodGroup FROM CustomerShipTo cs with (NOLOCK) INNER JOIN Customer c with (NOLOCK) ON c.CustomerCode = {0} AND cs.ShipToCode = c.DefaultShipToCode", DB.SQuote(basedOnCustomerCode)))
                {
                    if (reader.Read())
                    {
                        shippingMethodGroup = DB.RSField(reader, "ShippingMethodGroup");
                    }
                }
            }
            isRealTime = true;
            return shippingMethodGroup;
        }

        public static bool CheckCartInventoryIfTrimmed(Customer thisCustomer, bool limitCartToQuantityOnHand)
        {
            bool shouldTrim = false, isTrimmed = false;

            if (limitCartToQuantityOnHand)
            {
                using (SqlConnection con = DB.NewSqlConnection())
                {
                    con.Open();
                    using (IDataReader reader = DB.GetRSFormat(con, "exec EcommerceCheckCartAvailability @CartType = {0}, @CustomerCode = {1}, @WarehouseCode = {2}, @ContactCode = {3}", (int)CartTypeEnum.ShoppingCart, DB.SQuote(thisCustomer.CustomerCode), DB.SQuote(thisCustomer.WarehouseCode), DB.SQuote(thisCustomer.ContactCode)))
                    {// This data structure will hold the
                        // cart items with ItemType = Kit
                        // when one or more of their kit item details'
                        // quantity exceeds what is available on hand(Free Stock)
                        List<string> kitItemsToBeCleared = new List<string>();

                        while (reader.Read())
                        {
                            // NOTE :
                            //  Interprise uses the term FreeStock as Quantity currently on hand
                            //  and this also varies by warehouse.
                            //  When it comes to Kit Items, when one of the items in the kit 
                            //  has greater quantity or the current available free stock is 0 zero
                            //  just delete the kit item..
                            string itemCode = DB.RSField(reader, "ItemCode");
                            string cartId = DB.RSFieldGUID(reader, "ShoppingCartRecGuid");
                            string itemType = DB.RSField(reader, "ItemType");
                            string status = DB.RSField(reader, "Status");
                            string unitMeasureCode = DB.RSField(reader, "UnitMeasureCode");
                            bool shouldLimitCart = (status == "P") ? true : AppLogic.AppConfigBool("Inventory.LimitCartToQuantityOnHand");
                            bool isCBN = false;
                            int cbnItemId = 0;
                            decimal quantity = DB.RSFieldDecimal(reader, "Quantity");
                            decimal freeStock = decimal.Zero;
                            isCBN = DB.RSFieldBool(reader, "IsCBN");
                            cbnItemId = DB.RSFieldInt(reader, "CBNItemID");

                            if (isCBN && !cbnItemId.IsNullOrEmptyTrimmed())
                            {
                                string cbnUMCode = InterpriseHelper.GetCBNUnitMeasureCode(itemCode, unitMeasureCode);
                                Interprise.Facade.Base.CBN.CBNTransactionFacade cbnTransactionFacade = new Interprise.Facade.Base.CBN.CBNTransactionFacade();
                                freeStock = cbnTransactionFacade.ConfirmStockCount(cbnItemId.ToString(), quantity.ToString(), cbnUMCode);
                            }
                            else
                            {
                                freeStock = DB.RSFieldDecimal(reader, "FreeStock");
                            }

                            shouldTrim = (quantity > freeStock);

                            if (shouldTrim)
                            {
                                isTrimmed = true;
                                if (shouldLimitCart && itemType == Interprise.Framework.Base.Shared.Const.ITEM_TYPE_KIT)
                                {
                                    if (!kitItemsToBeCleared.Contains(cartId.ToLowerInvariant()))
                                    {
                                        kitItemsToBeCleared.Add(cartId.ToLowerInvariant());
                                    }
                                    // process later....
                                }
                                else if (shouldLimitCart)
                                {
                                    if (freeStock <= 0 && status == "A")
                                    {
                                        // there's no stock anymore, delete the item from the shopping cart and inform the user
                                        DB.ExecuteSQL("DELETE EcommerceShoppingCart WHERE ShoppingCartRecGuid = {0}", DB.SQuote(cartId));
                                    }
                                    else
                                    {
                                        // trim the shopping cart to the number of available free stock
                                        DB.ExecuteSQL("UPDATE EcommerceShoppingCart SET Quantity = {0} WHERE ShoppingCartRecGuid = {1}", Convert.ToInt32(freeStock), DB.SQuote(cartId));
                                    }
                                }
                            }
                        }

                        //  Now process the kit cart items whose details
                        //  has exceeded the quantity on hand/Free Stock
                        if (kitItemsToBeCleared.Count > 0)
                        {
                            isTrimmed = true;
                            foreach (string cartId in kitItemsToBeCleared)
                            {
                                // remove the kit from the shopping cart
                                DB.ExecuteSQL("DELETE EcommerceShoppingCart WHERE ShoppingCartRecGuid = {0}", DB.SQuote(cartId));

                                // clear it's details
                                DB.ExecuteSQL("DELETE EcommerceKitCart WHERE CartID = {0}", DB.SQuote(cartId));
                            }
                        }
                    }
                }
            }

            return isTrimmed;
        }

        public static bool CheckIfShouldUpdateCartMinimumQuantity(string customerCode)
        {
            Dictionary<Guid, decimal> itemsThatShouldMinimize = new Dictionary<Guid, decimal>();

            string sql = "SELECT wsc.ShoppingCartRecGuid, wsc.ItemCode, wsc.Quantity, ISNULL(iiwo.MinOrderQuantity,0) AS MinOrderQuantity ";
            sql += "FROM EcommerceShoppingCart wsc with (NOLOCK)";
            sql += "INNER JOIN InventoryItem ii with (NOLOCK) ON ii.ItemCode = wsc.ItemCode ";
            sql += "INNER JOIN InventoryItemWebOption iiwo with (NOLOCK) ON iiwo.ItemCode = ii.ItemCode ";
            sql += "WHERE wsc.CustomerCode = {0} AND wsc.Quantity < iiwo.MinOrderQuantity ";
            sql += "AND iiwo.WebSiteCode = {1}";


            using (SqlConnection con = DB.NewSqlConnection())
            {
                con.Open();
                using (IDataReader reader = DB.GetRSFormat(con, sql, DB.SQuote(customerCode), DB.SQuote(InterpriseHelper.ConfigInstance.WebSiteCode)))
                {
                    while (reader.Read())
                    {
                        Guid id = new Guid(DB.RSFieldGUID(reader, "ShoppingCartRecGuid"));
                        decimal minOrderQuantity = DB.RSFieldDecimal(reader, "MinOrderQuantity");
                        itemsThatShouldMinimize.Add(id, minOrderQuantity);
                    }
                }
            }

            // minimize the orders
            foreach (Guid key in itemsThatShouldMinimize.Keys)
            {
                int qty = Convert.ToInt32(itemsThatShouldMinimize[key]);
                DB.ExecuteSQL("UPDATE EcommerceShoppingCart SET Quantity = {0} WHERE ShoppingCartRecGuid = {1}", qty, DB.SQuote(key.ToString()));
            }

            return (itemsThatShouldMinimize.Count > 0);
        }

        public static string ShowPaymentTermOptions(Customer thisCustomer, string currentlySelectedPaymentTerm)
        {
            StringBuilder output = new StringBuilder();
            if (thisCustomer != null)
            {
                Dictionary<string, List<KeyValuePair<string, string>>> paymentTermOptionDictionary = new Dictionary<string, List<KeyValuePair<string, string>>>();

                // Build our data structure first
                string customerCode = CommonLogic.IIF(thisCustomer.IsNotRegistered, thisCustomer.AnonymousCustomerCode, thisCustomer.CustomerCode);


                using (SqlConnection con = DB.NewSqlConnection())
                {
                    con.Open();
                    using (IDataReader reader = DB.GetRSFormat(con, "exec eCommerceGetPaymentTermGroup @CustomerCode = {0}", DB.SQuote(customerCode)))
                    {
                        while (reader.Read())
                        {
                            // NOTE : 
                            //  Payment Method is case-sensitive on our treatment here
                            //  Therefore it must always match with similar payment methods
                            string paymentMethod = DB.RSField(reader, "DefaultPaymentMethod");
                            string paymentTermCode = DB.RSField(reader, "PaymentTermCode");
                            string paymentTermDescription = DB.RSField(reader, "PaymentTermDescription");

                            if (!paymentTermOptionDictionary.ContainsKey(paymentMethod))
                            {
                                paymentTermOptionDictionary.Add(paymentMethod, new List<KeyValuePair<string, string>>());
                            }

                            List<KeyValuePair<string, string>> paymentTermList = paymentTermOptionDictionary[paymentMethod];
                            paymentTermList.Add(new KeyValuePair<string, string>(paymentTermCode, paymentTermDescription));
                        }
                    }
                }

                //  once we reach here, our data structure is set-up
                //  next we build our html user interface

                //  Render our user interface as:
                //  Header
                //      Payment Term 1
                //      Payment Term 2

                //  The javascript function called below will be referencing this form element by it's name
                output.Append("<input id=\"SelectedPaymentTerm\" name=\"SelectedPaymentTerm\" type=\"hidden\" />");

                output.Append("<table>");
                foreach (string paymentMethod in paymentTermOptionDictionary.Keys)
                {

                    List<KeyValuePair<string, string>> paymentTermList = paymentTermOptionDictionary[paymentMethod];
                    int id = 0;
                    foreach (KeyValuePair<string, string> paymentTerm in paymentTermList)
                    {
                        output.Append("<tr>");
                        output.Append("<td>");
                        // NOTE :
                        //  The associated javascript function is located in an external file particular to the page
                        //  that will be calling this function. checkoutpayment.js
                        output.AppendFormat(
                            "<input type=\"radio\" id=\"PaymentTerm{0}\" name=\"PaymentSelection\" onclick=\"selectPaymentTerm(this, '{1}');\" {2} />",
                            ++id,
                            HttpUtility.HtmlEncode(paymentTerm.Key),
                            CommonLogic.IIF(
                                !string.IsNullOrEmpty(currentlySelectedPaymentTerm) && currentlySelectedPaymentTerm == paymentTerm.Key,
                                "checked=\"CHECKED\"",
                                string.Empty
                            )
                        );
                        output.AppendFormat("{0} - {1}", HttpUtility.HtmlEncode(paymentTerm.Key), HttpUtility.HtmlEncode(paymentTerm.Value));
                        output.Append("</td>");
                        output.Append("</tr>");
                    }
                }

                output.Append("</table>");
            }

            return output.ToString();
        }

        public const string PAYMENT_METHOD_CHECK = "Check/Cheque";
        public const string PAYMENT_METHOD_CREDITCARD = "Credit Card";
        public const string PAYMENT_METHOD_CASH = "Cash/Other";
        public const string PAYMENT_METHOD_WEBCHECKOUT = "Web Checkout";

        public static readonly DateTime SmallDateTimeMinValue = new DateTime(1900, 1, 1);

        public static void UpdateCustomerPaymentTerm(Customer thisCustomer, string paymentTermCode)
        {
            if (thisCustomer.IsRegistered)
            {
                DB.ExecuteSQL(
                    "UPDATE cs SET cs.PaymentTermCode = {0} FROM CRMContact c INNER JOIN CustomerShipTo cs ON cs.ShipToCode = c.DefaultShippingCode WHERE c.ContactCode = {1}",
                    DB.SQuote(paymentTermCode),
                    DB.SQuote(thisCustomer.ContactCode)
                );
            }
            else
            {
                DB.ExecuteSQL(
                    "UPDATE EcommerceCustomer SET PaymentTermCode = {0} WHERE CustomerID = {1}",
                    DB.SQuote(paymentTermCode),
                    thisCustomer.CustomerCode
                );
            }

            thisCustomer.PaymentTermCode = paymentTermCode;
        }

        public static void ValidatePaymentTerm(string paymentTermCode)
        {
            bool isValid = false;

            using (SqlConnection con = DB.NewSqlConnection())
            {
                con.Open();
                using (IDataReader reader = DB.GetRSFormat(con, "SELECT 1 FROM SystemPaymentTerm with (NOLOCK) WHERE PaymentTermCode = {0}", DB.SQuote(paymentTermCode)))
                {
                    isValid = reader.Read();
                }
            }

            if (!isValid) throw new ArgumentException("SECURITY EXCEPTION!!!");
        }

        public static bool GetCustomerCouponIfAny(string customerCode, ref string couponCode, bool isCustomerRegistered)
        {
            bool hasCoupon = false;
            if (!customerCode.IsNullOrEmptyTrimmed())
            {
                string couponQuery;
                if (isCustomerRegistered)
                {
                    couponQuery = string.Format("SELECT CouponCode FROM Customer with (NOLOCK) WHERE CustomerCode = {0}", DB.SQuote(customerCode));
                }
                else
                {
                    couponQuery = string.Format("SELECT CouponCode FROM EcommerceCustomer with (NOLOCK) WHERE CustomerCode = {0}", DB.SQuote(customerCode));
                }

                using (SqlConnection con = DB.NewSqlConnection())
                {
                    con.Open();
                    using (IDataReader reader = DB.GetRSFormat(con, couponQuery))
                    {
                        hasCoupon = reader.Read() &&
                                reader["CouponCode"] != null &&
                                reader["CouponCode"] != DBNull.Value &&
                                !string.IsNullOrEmpty((string)reader["CouponCode"]);

                        if (hasCoupon)
                            couponCode = (string)reader["CouponCode"];
                    }
                }

                return hasCoupon;
            }

            return hasCoupon;
        }

        public static bool IsItemCanAvailOfCoupon(string itemCode, string couponCode)
        {
            bool canAvail = false;


            using (SqlConnection con = DB.NewSqlConnection())
            {
                con.Open();
                using (IDataReader reader = DB.GetRSFormat(con, "exec eCommerceCanItemAvailOfCouponDiscount @ItemCode = {0}, @CouponCode = {1}", DB.SQuote(itemCode), DB.SQuote(couponCode)))
                {
                    canAvail = reader.Read() && DB.RSFieldBool(reader, "CanAvail");
                }
            }

            return canAvail;
        }

        public static decimal GetCouponDiscountApplied(InterpriseShoppingCart cart, decimal taxTotal, decimal freightTotal, string couponCode = "")
        {
            Customer thisCustomer = cart.ThisCustomer;
            string customerCode = CommonLogic.IIF(thisCustomer.IsRegistered, thisCustomer.CustomerCode, thisCustomer.CustomerID);
            decimal appliedDiscount = decimal.Zero;

            if (couponCode != string.Empty)
            {
            
                string couponType = string.Empty;
                string discountType = string.Empty;
                decimal discountPercent = decimal.Zero;
                decimal discountAmount = decimal.Zero;

                // NOTE :
                //  DiscountPercent = Float in Database Table so would convert to Double
                //  DiscountAmount = Money

                using (SqlConnection con = DB.NewSqlConnection())
                {
                    con.Open();
                    using (IDataReader reader = DB.GetRSFormat(con, String.Format(CommonLogic.IIF(thisCustomer.IsRegistered, "SELECT CouponID, CouponType, DiscountType, CAST(DiscountPercent AS NUMERIC(14,6)) AS DiscountPercent, CAST(DiscountAmount  AS NUMERIC(14,6)) AS DiscountAmount FROM CustomerCouponView with (NOLOCK) WHERE CustomerCode = {0} AND CouponCode = {1}",
                                                                "SELECT CSC.CouponID, CSC.CouponType, CSC.DiscountType, CAST(CSC.DiscountPercent AS NUMERIC(14,6)) AS DiscountPercent, CAST(CSC.DiscountAmount AS NUMERIC(14,6)) AS DiscountAmount FROM CustomerSalesCoupon CSC WITH (NOLOCK) CROSS JOIN EcommerceCustomer EC with (NOLOCK) WHERE EC.CustomerCode = {0} AND CSC.CouponCode = {1}"),
                                                                DB.SQuote(customerCode), DB.SQuote(couponCode))))
                    {
                        if (reader.Read())
                        {
                            couponType = DB.RSField(reader, "CouponType");
                            discountType = DB.RSField(reader, "DiscountType");
                            discountPercent = DB.RSFieldDecimal(reader, "DiscountPercent");
                            discountAmount = DB.RSFieldDecimal(reader, "DiscountAmount");
                        }
                    }
                }

                switch (couponType.ToLowerInvariant())
                {
                    case "products":
                        foreach (CartItem item in cart.CartItems)
                        {
                            // check first if this item is included in the coupon
                            if (IsItemCanAvailOfCoupon(item.ItemCode, couponCode))
                            {
                                decimal discountPrice = item.Price;
                                if (AppLogic.VATIsEnabled() &&
                                    thisCustomer.VATSettingReconciled == VatDefaultSetting.Inclusive)
                                {
                                    discountPrice = (item.Price - item.TaxRate);
                                }

                                appliedDiscount +=
                                ComputeCouponDiscount(cart.ThisCustomer,
                                    discountType,
                                    discountPrice,
                                    discountAmount,
                                    discountPercent);
                            }
                        }
                        break;
                    case "orders":
                        appliedDiscount =
                        ComputeCouponDiscount(cart.ThisCustomer,
                            discountType,
                            cart.GetCartSubTotal() + taxTotal + freightTotal,
                            discountAmount,
                            discountPercent);
                        break;
                    default:
                        throw new ArgumentException("Unknown coupon type!!!", couponType);
                }
            }

            return appliedDiscount;
        }

        public static decimal GetCouponDiscountApplied(InterpriseShoppingCart cart, string couponCode = "")
        {
            //Call the overload passing in 0 for the tax and freight amounts.
            return GetCouponDiscountApplied(cart, 0, 0, couponCode);
        }

        private static decimal ComputeCouponDiscount(Customer thisCustomer,
            string discountType,
            decimal totalToComputeAgainst,
            decimal discountAmount,
            decimal discountPercent)
        {
            decimal appliedDiscount = decimal.Zero;
            switch (discountType.ToLowerInvariant())
            {
                case "amount":
                    string homeCurrency = Currency.GetHomeCurrency();
                    string currencyCode = thisCustomer.CurrencyCode;

                    if (homeCurrency.Equals(currencyCode, StringComparison.InvariantCultureIgnoreCase))
                    {
                        appliedDiscount = discountAmount;
                    }
                    else
                    {
                        // perform conversion...
                        decimal exchangeRate = Currency.GetExchangeRate(currencyCode);

                        decimal convertedDiscountAmount =
                        Interprise.Facade.Base.SimpleFacade.Instance.ConvertCurrency(
                            exchangeRate,
                            discountAmount,
                            false,
                            currencyCode,
                            Interprise.Framework.Base.Shared.Enum.CurrencyFormat.Total
                        );
                        appliedDiscount = convertedDiscountAmount;
                    }
                    break;
                case "percent":
                    if (discountPercent > 0)
                    {
                        appliedDiscount = totalToComputeAgainst * (discountPercent / 100);
                    }
                    break;
            }

            return appliedDiscount;
        }


        public static bool IsCorrectCustomer(Customer thisCustomer, string orderNumber)
        {
            try
            {
                string billToCode = string.Empty;
                using (SqlConnection con = DB.NewSqlConnection())
                {
                    con.Open();
                    string format = "select BillToCode from CustomerSalesOrder so inner join (select SalesOrderCode, ItemCode from CustomerSalesOrderDetail where ItemType = 'Electronic Download') sod on sod.SalesOrderCode = so.SalesOrderCode where so.SalesOrderCode = " + DB.SQuote(orderNumber);
                    using (IDataReader dr = DB.GetRSFormat(con, format))
                    {
                        while (dr.Read())
                        {
                            billToCode = Convert.ToString(dr["BillToCode"]);
                        }
                    }
                }

                if (billToCode != thisCustomer.CustomerCode)
                {
                    return false;
                }
            }
            catch
            {
                return false;
            }

            return true;
        }

        /// <summary>
        /// Get Connected Business dabatase version where InterpriseSuiteEcommerce is connected.
        /// </summary>
        /// <returns>Database version</returns>
        public static string GetISdbVersion()
        {
            String version = String.Empty, salt = String.Empty, vector = String.Empty, outversion;
            try
            {

                using (SqlConnection con = DB.NewSqlConnection())
                {
                    con.Open();
                    using (IDataReader dr = DB.GetRSFormat(con, "SELECT VersionNumber,VersionSalt,VersionVector FROM SystemVersion with (NOLOCK)"))
                    {
                        while (dr.Read())
                        {
                            version = Convert.ToString(dr["VersionNumber"]);
                            salt = Convert.ToString(dr["VersionSalt"]);
                            vector = Convert.ToString(dr["VersionVector"]);
                        }
                    }
                }

                Interprise.Licensing.Base.Services.CryptoServiceProvider tmpCrypto = new Interprise.Licensing.Base.Services.CryptoServiceProvider();
                outversion = tmpCrypto.Decrypt(Convert.FromBase64String(version), Convert.FromBase64String(salt), Convert.FromBase64String(vector));
                return outversion;
            }
            catch
            {
                return String.Empty;
            }
        }

        #region "report"
        /// <summary>
        /// Report generation.
        /// </summary>
        /// <param name="OrderNumber">Order number to view.</param>
        /// <returns>Report</returns>
        public static XtraReport CreateReport(string OrderNumber)
        {
            string m_ReportCode = string.Empty;
            XtraReport reportObject;

            using (SqlConnection con = DB.NewSqlConnection())
            {
                con.Open();
                using (IDataReader rs = DB.GetRSFormat(con, "SELECT OrderConfirmationReport FROM EcommerceSite with (NOLOCK) WHERE Websitecode = {0}", DB.SQuote(ConfigInstance.WebSiteCode)))
                {
                    while (rs.Read())
                    {
                        m_ReportCode = DB.RSField(rs, "OrderConfirmationReport");
                    }
                }
            }

            Interprise.Presentation.ReportCenter.ReportInterface reportInterface = new Interprise.Presentation.ReportCenter.ReportInterface();
            Interprise.Framework.Base.Report.ReportParameter reportParam = new Interprise.Framework.Base.Report.ReportParameter();
            reportParam.ReportCode = m_ReportCode;

            Interprise.Framework.Base.Report.ReportParameter.SimpleCriteria reportCriteria = new Interprise.Framework.Base.Report.ReportParameter.SimpleCriteria();
            reportCriteria.ColumnName = "SalesOrderCode";
            reportCriteria.BinaryOperator = Interprise.Framework.Base.Shared.Enum.BinaryCriteriaOperator.Equal;
            reportCriteria.ValueType = Interprise.Framework.Base.Shared.Enum.OperandValueType.Value;
            reportCriteria.Value = OrderNumber;

            reportParam.Criteria = reportCriteria;

            reportObject = reportInterface.GenerateReportWithData(reportParam);
            return reportObject;
        }

        public static Attachment ExportReportAsAttachment(string salesOrderCode)
        {
            XtraReport reportObject = CreateReport(salesOrderCode);

            MemoryStream exportToStream = new MemoryStream();
            string mimeType = "application/pdf";
            string extension = ".pdf";

            if (null != exportToStream)
            {
                switch (AppLogic.AppConfig("AttachmentFormat").ToUpperInvariant())
                {
                    case "PDF":
                        reportObject.ExportToPdf(exportToStream);
                        mimeType = "application/pdf";
                        extension = ".pdf";
                        break;

                    case "MHT":
                        reportObject.ExportToMht(exportToStream);
                        mimeType = "message/rfc822";
                        extension = ".mht";
                        break;

                    case "CSV":
                        reportObject.ExportToCsv(exportToStream);
                        mimeType = "text/plain";
                        extension = ".csv";
                        break;

                    case "HTML":
                        reportObject.ExportToHtml(exportToStream);
                        mimeType = "text/html";
                        extension = ".html";
                        break;

                    case "JPEG":
                        reportObject.ExportToImage(exportToStream);
                        mimeType = "image/jpeg";
                        extension = ".jpg";
                        break;

                    case "BMP":
                        reportObject.ExportToImage(exportToStream);
                        mimeType = "image/bmp";
                        extension = ".bmp";
                        break;

                    case "GIF":
                        reportObject.ExportToImage(exportToStream);
                        mimeType = "image/gif";
                        extension = ".gif";
                        break;

                    case "PNG":
                        reportObject.ExportToImage(exportToStream);
                        mimeType = "image/png";
                        extension = ".png";
                        break;

                    case "XLS":
                        reportObject.ExportToXls(exportToStream);
                        mimeType = "application/vnd.ms-excel";
                        extension = ".xls";
                        break;

                    case "TXT":
                        reportObject.ExportToText(exportToStream);
                        mimeType = "text/plain";
                        extension = ".txt";
                        break;

                    case "RTF":
                        reportObject.ExportToRtf(exportToStream);
                        mimeType = "application/rtf";
                        extension = ".rtf";
                        break;

                    default:
                        reportObject.ExportToPdf(exportToStream);
                        break;
                }
            }

            exportToStream.Seek(0, SeekOrigin.Begin);

            ContentType contentType = new ContentType(mimeType);
            contentType.Name = salesOrderCode + extension;

            Attachment data = new Attachment(exportToStream, contentType);

            return data;
        }

        #endregion

        private const string DOWNLOAD_LINK = "DownloadLink";

        public static void StoreAnonCustomerRedirectDownloadLinkUponRegistration(Customer thisCustomer, string downloadLink)
        {
            CustomerSession sess = new CustomerSession(thisCustomer.ContactGUID);
            sess.SetVal(DOWNLOAD_LINK, downloadLink, System.DateTime.Now.AddHours(1));
            sess = null;
        }

        public static void RedirectIfCustomerIsAboutToDownloadItem(Customer thisCustomer)
        {
            string downloadLink = string.Empty;
            downloadLink = thisCustomer.ThisCustomerSession[DOWNLOAD_LINK];

            if (!string.IsNullOrEmpty(downloadLink))
            {
                HttpContext.Current.Response.Redirect(downloadLink);
            }
        }

        public static void ClearCustomerDownloadableLinkFromSession(Customer thisCustomer)
        {
            thisCustomer.ThisCustomerSession.ClearVal(DOWNLOAD_LINK);
        }

        public static string GetIP4Address()
        {
            string IP4Address = String.Empty;

            foreach (IPAddress IPA in Dns.GetHostAddresses(HttpContext.Current.Request.UserHostAddress))
            {
                if (IPA.AddressFamily.ToString() == "InterNetwork")
                {
                    IP4Address = IPA.ToString();
                    break;
                }
            }

            if (IP4Address != String.Empty)
            {
                return IP4Address;
            }

            foreach (IPAddress IPA in Dns.GetHostAddresses(Dns.GetHostName()))
            {
                if (IPA.AddressFamily.ToString() == "InterNetwork")
                {
                    IP4Address = IPA.ToString();
                    break;
                }
            }

            return IP4Address;
        }

        #region GetPasswordEmailTemplate

        public static string GetPasswordEmailTemplate(string emailAdd)
        {
            string contactCode = String.Empty;
            return GetPasswordEmailTemplate(emailAdd, AppLogic.GetContactCodeByEmail(emailAdd));
        }

        public static string GetPasswordEmailTemplate(string emailAdd, string contactCode)
        {
            string returnstring = String.Empty;
            if (contactCode.IsNullOrEmptyTrimmed()) { return returnstring; }
            if (emailAdd.IsNullOrEmptyTrimmed()) { return returnstring; }

            if (!AppLogic.GetContactCodeByEmail(emailAdd).IsNullOrEmptyTrimmed())
            {
                string decryptedPassword = Customer.Current.GetPassword(contactCode);
                var addParam = new List<XmlPackageParam> { };
                addParam.Add(new XmlPackageParam("CurrentContactCode", contactCode));
                addParam.Add(new XmlPackageParam("EmailAddress", emailAdd));
                addParam.Add(new XmlPackageParam("WebSiteCode", InterpriseConfiguration.Instance.WebSiteCode));
                addParam.Add(new XmlPackageParam("Password", decryptedPassword));

                returnstring = AppLogic.RunXmlPackage(
                       "notification.requestnewpassword.xml.config",
                       null,
                       Customer.Current,
                       Customer.Current.SkinID,
                       String.Empty,
                       addParam,
                       true,
                       true);

            }

            return returnstring;
        }

        #endregion

        #region ProductCompare

        public static string[] GetProductCompareImageLinks(int[] itemCounters)
        {
            int arraylength = AppLogic.AppConfigNativeInt("MaxItemToCompare");

            if (arraylength <= 1) { arraylength = 5; }

            Customer thisCustomer = Customer.Current;
            String ImgUrl = String.Empty;
            string displayName = string.Empty;
            bool existing; bool exists;
            string ImgFilename = string.Empty;
            StringBuilder tmpS = new StringBuilder();
            string accItemCode = string.Empty;

            string[] returnstring = new string[arraylength];

            int i = 0;
            foreach (int itemCounter in itemCounters)
            {
                using (SqlConnection con = DB.NewSqlConnection())
                {
                    con.Open();
                    using (IDataReader reader = DB.GetRSFormat(con,

                        "SELECT I.ItemCode, " +
                        "(SELECT IOA.[FileName] FROM InventoryOverrideImage IOA WHERE IOA.ItemCode = I.ItemCode  AND IOA.WebSiteCode={0} AND IsDefaultIcon = 1) AS [FileName] " +
                        ",I.[ItemDescription]  FROM InventoryItemView I " +
                        "WHERE I.[Counter] = {1} AND I.LanguageCode ={2} "
                        ,

                    DB.SQuote(InterpriseHelper.ConfigInstance.WebSiteCode), itemCounter.ToString(), DB.SQuote(thisCustomer.LanguageCode)))
                    {

                        ImgFilename = string.Empty;
                        accItemCode = string.Empty;
                        displayName = string.Empty;

                        existing = reader.Read();
                        if (existing)
                        {
                            ImgFilename = (DB.RSField(reader, "Filename"));
                            accItemCode = (DB.RSField(reader, "ItemCode"));
                            displayName = (DB.RSField(reader, "ItemDescription"));

                        }
                    }
                }

                if (!existing)
                {
                    accItemCode = GetInventoryItemCode(itemCounter);
                }

                ImgUrl = AppLogic.LocateImageFilenameUrl("Product", accItemCode, "minicart", ImgFilename, AppLogic.AppConfigBool("Watermark.Enabled"), out exists);

                tmpS.Clear();
                if (ImgUrl.Length != 0)
                {
                    string seTitle = "";
                    string seAltText = "";
                    AppLogic.GetSEImageAttributes(accItemCode, "minicart", thisCustomer.LanguageCode, ref seTitle, ref seAltText);

                    if (String.IsNullOrEmpty(displayName)) { displayName = seTitle; }

                    tmpS.Append("href=" + SE.MakeProductLink(itemCounter.ToString(), displayName));
                    tmpS.Append(Environment.NewLine + "src=" + ImgUrl + Environment.NewLine + "alt=" + seAltText + Environment.NewLine + "title=" + seTitle);

                }

                if (i < returnstring.Length)
                {
                    returnstring[i] = tmpS.ToString();
                }
                i = i + 1;
            }

            return returnstring;
        }

        public static string GetProductCompareXmlPackage(bool includejavascript, string xmlpackagename)
        {
            string returnstring = returnstring = "<div id = " + "\"" + "minicompare_panel" + "\"" + "align=" + "\"" + "left" + "\">" + "</div>";
            string cia = String.Empty;
            string maxnumber = "5";

            int intMax = AppLogic.AppConfigUSInt("MaxNumberOfItemToCompare");
            if (intMax < 2) { intMax = 2; }
            else if (intMax > 5) { intMax = 5; }

            maxnumber = intMax.ToString();

            HttpCookie myCookie = new HttpCookie("cia");
            myCookie = HttpContext.Current.Request.Cookies["cia"];

            if (myCookie != null)
            {
                cia = myCookie.Value;
                if (cia.Length == 0)
                {
                    cia = String.Empty;
                }
            }


            var xml = new System.Xml.Linq.XElement(DomainConstants.XML_ROOT_NAME);
            xml.Add(new System.Xml.Linq.XElement("ITEM_COUNTERS", cia));
            xml.Add(new System.Xml.Linq.XElement("ITEM_COUNTER", string.Empty));
            xml.Add(new System.Xml.Linq.XElement("COMPARETEXT", maxnumber));
            xml.Add(new System.Xml.Linq.XElement("CREATEBOX", Decimal.One.ToString()));
            xml.Add(new System.Xml.Linq.XElement("INCLUDESCRIPT", includejavascript.ToString()));

            var xmlpackage = new XmlPackage2(xmlpackagename, xml);
            returnstring = xmlpackage.TransformString();

            returnstring = "<div id = " + "\"" + "minicompare_panel" + "\"" + "align=" + "\"" + "left" + "\">" + returnstring + "</div>";
            return returnstring;
        }

        public static string CreateCompareCheckbox(int itemCounter, string xmlpackagename)
        {
            string returnstring = String.Empty;

            var xml = new System.Xml.Linq.XElement(DomainConstants.XML_ROOT_NAME);
            xml.Add(new System.Xml.Linq.XElement("ITEM_COUNTERS", String.Empty));
            xml.Add(new System.Xml.Linq.XElement("ITEM_COUNTER", itemCounter.ToString()));
            xml.Add(new System.Xml.Linq.XElement("COMPARETEXT", 0));
            xml.Add(new System.Xml.Linq.XElement("CREATEBOX", Decimal.Zero.ToString()));

            var xmlpackage = new XmlPackage2(xmlpackagename, xml);
            returnstring = xmlpackage.TransformString();

            return returnstring;
        }

        #endregion

       public static bool IsLeadEmailDuplicate(string email)
        {
            bool duplicate = false;

            SqlConnection con = DB.NewSqlConnection();

            try
            {
                con.Open();

                string sqlQuery = string.Format("SELECT * FROM CRMLead with (NOLOCK) WHERE EMail = {0}", DB.SQuote(email));
                IDataReader reader = DB.GetRSFormat(con, sqlQuery);

                if (reader.Read())
                {

                    duplicate = true;
                }

            }
            catch (Exception ex)
            {

                string error = ex.Message;
                duplicate = true;

            }
            finally
            {
                con.Close();
                con.Dispose();
            }

            return duplicate;
        }

       public static bool IsLeadDuplicate(string firstName, string middleName, string lastName)
        {
            bool duplicate = false;

            SqlConnection con = DB.NewSqlConnection();

            try
            {
                con.Open();

                string sqlQuery = string.Format("SELECT * FROM CRMLead with (NOLOCK) WHERE  ContactFirstName = {0} AND ContactLastName = {1}", DB.SQuote(firstName), DB.SQuote(lastName));

                IDataReader reader = DB.GetRSFormat(con, sqlQuery);

                if (reader.Read())
                {

                    duplicate = true;
                }

            }
            catch (Exception ex)
            {

                string error = ex.Message;
                duplicate = true;

            }
            finally
            {
                con.Close();
                con.Dispose();
            }

            return duplicate;
        }

       public static string CreateNewLead(List<string> list)
        {
            string msg = AppLogic.UNDEFINED_RESULT;

            try
            {

                using (Interprise.Framework.CRM.DatasetGateway.LeadDatasetGateway ds = new Interprise.Framework.CRM.DatasetGateway.LeadDatasetGateway())
                {
                    using (Interprise.Facade.CRM.LeadFacade facade = new Interprise.Facade.CRM.LeadFacade(ds))
                    {

                        facade.AddLead(String.Empty, String.Empty);
                        ds.CRMLeadView[0].BeginEdit();

                        string salutation = list[0];
                        string firstName = list[1];

                        string middleName = string.Empty;
                        string lastName = list[3];


                        string email = list[4];
                        string address = list[5];
                        string country = list[6];
                        string state = list[7];
                        string city = list[8];
                        string phone = list[9];
                        string message = list[10];
                        int skinId = int.Parse(list[11]);
                        string localeSettings = list[12];

                        string postalCode = list[13];
                        string fullName = string.Empty;

                        if (!string.IsNullOrEmpty(salutation))
                        {
                            ds.CRMLeadView[0].ContactSalutationCode = salutation;
                            fullName = string.Format("{0} {1} {2}", salutation, firstName, lastName);

                        }
                        else
                        {

                            fullName = string.Format("{0} {1}", firstName, lastName);
                        }

                        ds.CRMLeadView[0][Const.CRMLEADVIEW_CONTACTNAME_COLUMN] = fullName;

                        ds.CRMLeadView[0].LeadName = fullName;


                        ds.CRMLeadView[0].ContactFirstName = firstName;
                        ds.CRMLeadView[0].ContactMiddleName = middleName;
                        ds.CRMLeadView[0].ContactLastName = lastName;

                        ds.CRMLeadView[0].Address = address;
                        ds.CRMLeadView[0].EMail = email;
                        ds.CRMLeadView[0].Country = country;
                        ds.CRMLeadView[0].State = state;
                        ds.CRMLeadView[0].City = city;

                        #region Postal Code Handler

                        if (country == DomainConstants.COUNTRY_US)
                        {
                            int digits = GetPostalCodeDigits(postalCode, false);
                            ds.CRMLeadView[0].PostalCode = CommonLogic.IIF(digits == 0, postalCode, digits.ToString("00000.##"));

                            int plus4 = GetPostalCodeDigits(postalCode, true);
                            if (plus4 > 0) ds.CRMLeadView[0].Plus4 = plus4;
                        }
                        else
                        {
                            ds.CRMLeadView[0].PostalCode = postalCode;
                        }

                        #endregion

                        string county = list[14];
                        if (!string.IsNullOrEmpty(county)) ds.CRMLeadView[0].County = county;

                        ds.CRMLeadView[0].PhoneNumber = phone;
                        ds.CRMLeadView[0].SourceCode = AppLogic.SOURCE_CODE;
                        ds.CRMLeadView[0].AssignedTo = InterpriseSuiteEcommerceCommon.InterpriseHelper.ConfigInstance.UserCode;
                        ds.CRMLeadView[0].WebSiteCode = InterpriseHelper.ConfigInstance.WebSiteCode;

                        // Added below line to get the affiliate id from session cookie, affiliate id will then be assigned to lead

                        ds.CRMLeadView[0].SalesRepGroupCode = Customer.RecordAffiliateSessionCookie();

                        using (System.Windows.Forms.RichTextBox rtf = new System.Windows.Forms.RichTextBox())
                        {
                            System.Text.StringBuilder details = new System.Text.StringBuilder();



                            details.AppendLine("Message: " + message);

                            details.AppendLine();
                            rtf.Text = details.ToString();

                            ds.CRMLeadView[0].DetailsRtf = rtf.Rtf;
                            ds.CRMLeadView[0].DetailsText = rtf.Text;
                            ds.CRMLeadView[0].EndEdit();

                            bool updateSucceeded = true;

                            updateSucceeded = facade.UpdateDataSet(new string[][] { new string[] { ds.CRMLeadView.TableName, "CREATECRMLEAD", "UPDATECRMLEAD", "DELETECRMLEAD" } }, Interprise.Framework.Base.Shared.Enum.TransactionType.CRMLead, string.Empty, false);

                            if (updateSucceeded)
                            {

                                StringBuilder leadDetails = new StringBuilder();

                                leadDetails.AppendFormat("leadname={0}", fullName);


                                leadDetails.AppendFormat("&country={0}", country);
                                leadDetails.AppendFormat("&state={0}", state);
                                leadDetails.AppendFormat("&city={0}", city);
                                leadDetails.AppendFormat("&email={0}", email);
                                leadDetails.AppendFormat("&phone={0}", phone);
                                leadDetails.AppendFormat("&message={0}", message);


                                AppLogic.SendEmailNotification(skinId, localeSettings, leadDetails.ToString());

                                msg = updateSucceeded.ToString();
                            }
                            else
                            {

                                if (facade.CurrentDataset.HasErrors)
                                {
                                    DataSet dserror = facade.CurrentDataset.GetChanges();
                                    DataColumn[] cols;

                                    bool errorFound = false;
                                    string error = string.Empty;

                                    foreach (DataTable t in dserror.Tables)
                                    {

                                        foreach (DataRow r in t.Rows)
                                        {

                                            cols = r.GetColumnsInError();

                                            foreach (DataColumn c in cols)
                                            {

                                                error = r.GetColumnError(c.ColumnName);
                                                errorFound = true;
                                                break;

                                            }

                                            if (errorFound)
                                            {
                                                break;
                                            }
                                        }

                                        if (errorFound)
                                        {
                                            break;
                                        }

                                    }


                                    if (error.Length > 0)
                                    {
                                        msg = error;
                                    }

                                }
                                else
                                {
                                    msg = updateSucceeded.ToString();
                                }

                            }

                        }
                    }
                }


            }
            catch (Exception ex)
            {

                msg = ex.Message;

            }

            return msg;
            
        }

       public static string SaveCaseForm(List<string> list)
        {

            string msg = AppLogic.UNDEFINED_RESULT;

            try
            {

                using (Interprise.Framework.Base.DatasetGateway.CRM.ActivityDatasetGateway ds = new Interprise.Framework.Base.DatasetGateway.CRM.ActivityDatasetGateway())
                {

                    using (Interprise.Facade.Base.CRM.ActivityFacade facade  = new Interprise.Facade.Base.CRM.ActivityFacade(ds))
                    {

                        facade.AddActivity(string.Empty, string.Empty, DateTime.Now, Interprise.Framework.Base.Shared.Enum.ActivityType.Case.ToString(), Interprise.Framework.Base.Shared.Enum.TransactionType.CustomerActivity, null, null);
                        ds.CRMActivityView[0].BeginEdit();

                        string entityCode = Customer.Current.AnonymousCustomerCode;

                        if (Customer.Current.IsRegistered)
                        {

                            entityCode = Customer.Current.CustomerCode;

                        }

                        string contactName  = list[0];
                        string emailAddress = list[1];
                        string areaCode     = list[2];
                        string primaryPhone = list[3];
                        string country      = list[4];
                        string state        = list[5];
                        string postalCode   = list[6];
                        string city         = list[7];
                        string subject      = list[8];
                        string address      = list[9];
                        string problem      = list[10];
                        string priority     = Interprise.Framework.Base.Shared.Enum.Priority.High.ToString();
                        string county       = list[11];

                        StringBuilder profile = new StringBuilder();
                        profile.Append(contactName);
                        profile.AppendLine(string.Format("+({0}) {1}", areaCode, primaryPhone));
                        profile.AppendLine(emailAddress);

                        StringBuilder location = new StringBuilder();

                        location.AppendLine(country);
                        location.AppendLine(address);
                        location.AppendLine(postalCode);
                        location.AppendLine(string.Format("{0}, {1}", city, state));
                        
                        if (!string.IsNullOrEmpty(county)) location.AppendLine(county);

                        StringBuilder problemStr = new StringBuilder();

                        problemStr.Append(problem);
                        problemStr.AppendLine();
                        problemStr.AppendLine();
                        problemStr.AppendLine(profile.ToString());

                        ds.CRMActivityView[0].Type = Interprise.Framework.Base.Shared.Enum.ActivityType.Case.ToString();
                        ds.CRMActivityView[0].ParentType = Interprise.Framework.Base.Shared.Enum.TransactionType.CustomerActivity.ToString();
                        ds.CRMActivityView[0].EntityCode = entityCode;
                        ds.CRMActivityView[0].ContactCode = Customer.Current.ContactCode;
                        ds.CRMActivityView[0].Priority = priority;
                        ds.CRMActivityView[0].Location = location.ToString();
              

                        using (System.Windows.Forms.RichTextBox rtf = new System.Windows.Forms.RichTextBox())
                        {
                            System.Text.StringBuilder details = new System.Text.StringBuilder();
                            rtf.Text = problemStr.ToString();

                            ds.CRMActivityView[0].Subject = subject;
                            ds.CRMActivityView[0].ProblemRtf = rtf.Rtf;
                            ds.CRMActivityView[0].ProblemText = rtf.Text;
                            
                        }


                        ds.CRMActivityView[0].EndEdit();

                        bool updateSucceeded = true;

                        updateSucceeded = facade.UpdateDataSet(new string[][] { new string[] { ds.CRMActivityView.TableName, "CREATECRMACTIVITY", "UPDATECRMACTIVITY", "DELETECRMACTIVITY" } }, Interprise.Framework.Base.Shared.Enum.TransactionType.CustomerActivity, string.Empty, false);

                        if (updateSucceeded)
                        {
                            msg = updateSucceeded.ToString();
                        }
                        else
                        {

                            if (facade.CurrentDataset.HasErrors)
                            {
                                DataSet dserror = facade.CurrentDataset.GetChanges();
                                DataColumn[] cols;

                                bool errorFound = false;
                                string error = string.Empty;

                                foreach (DataTable t in dserror.Tables)
                                {

                                    foreach (DataRow r in t.Rows)
                                    {

                                        cols = r.GetColumnsInError();

                                        foreach (DataColumn c in cols)
                                        {

                                            error = r.GetColumnError(c.ColumnName);
                                            errorFound = true;
                                            break;

                                        }

                                        if (errorFound)
                                        {
                                            break;
                                        }
                                    }

                                    if (errorFound)
                                    {
                                        break;
                                    }

                                }


                                if (error.Length > 0)
                                {
                                    msg = error;
                                }

                            }
                            else
                            {
                                msg = updateSucceeded.ToString();
                            }

                        }

                    }
                }
            }catch (Exception ex){

                        msg = ex.Message;

                    }

            return msg;
                
        }
       
       public static string ShowInventoryAccessoryOptions(string itemCode, bool showPicture, int maxNumberOfRecordsToDisplay, string teaser, Customer thisCustomer, bool includeJavascript, bool includeDefaultContainer, ViewingPage page, string xmlHelperTemplate)
        {
            string output = string.Empty;
            string displayFormat = AppLogic.AppConfig("AccessoryProductsFormat");
            string ImgFilename = string.Empty;
            bool exists = false;

            var tmpS = new StringBuilder();
            string customerCode = string.Empty;
            switch (page)
            {
                case ViewingPage.Product:
                    customerCode = CommonLogic.IIF(thisCustomer.IsNotRegistered, thisCustomer.AnonymousCustomerCode, thisCustomer.CustomerCode);
                    break;
                case ViewingPage.ShoppingCart:
                    customerCode = thisCustomer.CustomerCode;
                    break;
            }

            string query = string.Format("exec EcommerceGetAccessoryItems @CustomerCode = {0}, @WebSiteCode = {1}, @ItemCode = {2}, @LanguageCode = {3}, @CurrentDate = {4}, @ProductFilterID = {5}, @ContactCode = {6}",
                    DB.SQuote(customerCode),
                    DB.SQuote(ConfigInstance.WebSiteCode),
                    DB.SQuote(itemCode),
                    DB.SQuote(thisCustomer.LanguageCode),
                    DB.SQuote(Localization.DateTimeStringForDB(DateTime.Now)), 
                    DB.SQuote(thisCustomer.ProductFilterID),
                    DB.SQuote(thisCustomer.ContactCode));

            var ds = DB.GetDS(query, false);
            if (ds.Tables[0].Rows.Count > 0)
            {
                var xml = new XElement(DomainConstants.XML_ROOT_NAME);
                xml.Add(new XElement(DomainConstants.XML_SECTION_TYPE, XMLSectionType.DISPLAY_ACCESSORIES));
                xml.Add(new XElement("INCLUDE_SCRIPT", includeJavascript.ToString().ToLowerInvariant()));
                xml.Add(new XElement("ACCESSORIES_HEADER_TEXT", AppLogic.GetString("mobile.showproduct.aspx.cs.51", thisCustomer.SkinID, thisCustomer.LocaleSetting)));
                xml.Add(new XElement("ACCESSORIES_TABLE_FORMAT", displayFormat));

                string htmlTeaser = teaser;
                if (teaser.Length == 0)
                {
                    var UpsellTeaser = new Topic("UpsellTeaser", thisCustomer.LocaleSetting, thisCustomer.SkinID, null);
                    htmlTeaser = UpsellTeaser.Contents.Replace("(!SKINID!)", thisCustomer.SkinID.ToString());
                }

                xml.Add(new XElement("UPSALE_TEXT", htmlTeaser));

                bool empty = (ds.Tables[0].Rows.Count > 0);
                bool isWholesaleOnlySite = (AppLogic.AppConfigBool("WholesaleOnlySite") && thisCustomer.DefaultPrice.ToLower() != "wholesale");
                xml.Add(new XElement("IS_WHOLESALE_ONLYSITE", isWholesaleOnlySite.ToString().ToLowerInvariant()));

                //int defaultItemPerAccessories = 4;
                //int itemsPerRow = AppLogic.AppConfigUSInt("AccessoryGridColWidth");
                //if (itemsPerRow == 0)
                //{
                //    itemsPerRow = maxNumberOfRecordsToDisplay;
                //}

                var dataRows = ds.Tables[0].Rows
                                           .OfType<DataRow>()
                                           .Take(maxNumberOfRecordsToDisplay)
                                           .ToArray();

                foreach (DataRow row in dataRows)
                {
                    var substituteItems = new XElement("SUBSTITUTE_ITEMS");
                    int itemCounter = DB.RowFieldInt(row, "Counter");
                    string accessItemCode = InterpriseHelper.GetInventoryItemCode(itemCounter);
                    substituteItems.Add(new XElement("ITEM_COUNTER", itemCounter));
                    substituteItems.Add(new XElement("ACCESS_ITEM_CODE", accessItemCode));

                    ImgFilename = string.Empty;
                    string displayName = DB.RowField(row, "ItemDescription");
                    if (CommonLogic.IsStringNullOrEmpty(displayName))
                    {
                        displayName = DB.RowField(row, "AccessoryName");
                    }

                    string itemLink = SE.MakeProductLink(itemCounter.ToString(), displayName);
                    substituteItems.Add(new XElement("ITEM_LINK", itemLink));
                    substituteItems.Add(new XElement("ITEM_NAME", displayName));

                    bool validAccessory = true;
                    if (DB.RowField(row, "ItemType") == Interprise.Framework.Base.Shared.Const.ITEM_TYPE_ELECTRONIC_DOWNLOAD)
                    {
                        var download = DownloadableItem.FindByItemCode(DB.RowField(row, "AccessoryCode"));
                        if (download == null)
                        {
                            validAccessory = false;
                        }
                        else if (!download.IsPhysicalFileExisting())
                        {
                            validAccessory = false;
                        }
                    }

                    if (showPicture)
                    {
                        var xmlImage = new XElement("IMAGE");
                        xmlImage.Add(new XElement("ITEM_LINK", itemLink));

                        string ImgUrl = string.Empty;
                        string accItemCode = InterpriseHelper.GetInventoryItemCode(itemCounter);
                        ImgFilename = ProductImage.GetImageFileNameFromInventory("AND IsDefaultIcon = 1", DB.SQuote(accItemCode), ImageSizeTypes.icon.ToString());
                        ImgUrl = AppLogic.LocateImageFilenameUrl("Product", accItemCode, "icon", ImgFilename, AppLogic.AppConfigBool("Watermark.Enabled"), out exists);
                        xmlImage.Add(new XElement("IMG_URL", ImgUrl));

                        if (ImgUrl.Length != 0)
                        {
                            string seTitle = string.Empty;
                            string seAltText = string.Empty;
                            AppLogic.GetSEImageAttributes(accItemCode, ImageSizeTypes.icon.ToString(), thisCustomer.LanguageCode, ref seTitle, ref seAltText);
                            xmlImage.Add(new XElement("IMG_ALT_TEXT", seAltText));
                            xmlImage.Add(new XElement("IMG_TITLE_TEXT", seTitle));
                        }

                        substituteItems.Add(xmlImage);
                    }

                    //Handled here the checking of format. Difference is the EXTENDED TEXT
                    if (displayFormat.ToUpperInvariant() == "TABLE")
                    {
                        substituteItems.Add(new XElement("SHOW_EXTENDED_TEXT", AppLogic.GetString("common.cs.16", thisCustomer.SkinID, thisCustomer.LocaleSetting)));
                        string webDescription = DB.RowFieldByLocale(row, "WebDescription", thisCustomer.LocaleSetting);
                        if (!webDescription.IsNullOrEmptyTrimmed())
                        {
                            if (AppLogic.ReplaceImageURLFromAssetMgr)
                            {
                                webDescription = webDescription.Replace("../images", "images");
                            }
                        }
                        else
                        {
                            webDescription = DB.RowFieldByLocale(row, "ItemDescription", thisCustomer.LocaleSetting);
                        }

                        substituteItems.Add(new XElement("EXTENDED_DESCRIPTION", webDescription));
                    }

                    substituteItems.Add(new XElement("IS_WHOLESALE_ONLYSITE", isWholesaleOnlySite.ToString().ToLowerInvariant()));
                    if (!isWholesaleOnlySite)
                    {
                        bool vatEnabled = AppLogic.AppConfigBool("VAT.Enabled");
                        substituteItems.Add(new XElement("VAT_ENABLED", vatEnabled.ToString().ToLowerInvariant()));

                        bool withVat = vatEnabled && thisCustomer.VATSettingReconciled == VatDefaultSetting.Inclusive;
                        decimal promotionalPrice = decimal.Zero;

                        var um = UnitMeasureInfo.ForItem(accessItemCode, UnitMeasureInfo.ITEM_DEFAULT);
                        decimal price = InterpriseHelper.GetSalesPriceAndTax(thisCustomer.CustomerCode, accessItemCode, thisCustomer.CurrencyCode, decimal.One, um.Code, withVat, ref promotionalPrice);
                        if (promotionalPrice != decimal.Zero)
                        {
                            price = promotionalPrice;
                        }

                        substituteItems.Add(new XElement("ITEM_PRICE", FormatCurrencyForCustomer(price, thisCustomer.CurrencyCode)));
                        if (vatEnabled)
                        {
                            string vatText = string.Empty;
                            if (thisCustomer.VATSettingReconciled == VatDefaultSetting.Inclusive)
                            {
                                vatText = AppLogic.GetString("showproduct.aspx.38", thisCustomer.SkinID, thisCustomer.LocaleSetting);
                            }
                            else
                            {
                                vatText = AppLogic.GetString("showproduct.aspx.37", thisCustomer.SkinID, thisCustomer.LocaleSetting);
                            }
                            substituteItems.Add(new XElement("VAT_TEXT", vatText));
                        }
                    }
                    substituteItems.Add(new XElement("IS_VALID_ACCESSORY", validAccessory.ToString().ToLowerInvariant()));
                    xml.Add(substituteItems);
                }

                output = new XmlPackage2(xmlHelperTemplate, xml).TransformString();
            }

            return output;
        }

       public static string ShowInventorySubstituteOptions(string itemCode, int showNum, bool showPics, string teaser, Customer thisCustomer, string helperTemplate)
        {
            string RelatedProductList = String.Empty;
            string ImgFilename = string.Empty;
            bool exists = false;

            var tmpS = new StringBuilder();
            string displayFormat = AppLogic.AppConfig("SubstituteProductsFormat");

            var xml = new XElement(DomainConstants.XML_ROOT_NAME);
            xml.Add(new XElement(DomainConstants.XML_SECTION_TYPE, XMLSectionType.DISPLAY_SUBSTITUTEPRODUCT));
            xml.Add(new XElement("SUBSTITUE_TABLE_FORMAT", displayFormat));
            xml.Add(new XElement("SUBSTITUTE_HEADER_TEXT", AppLogic.GetString("mobile.showproduct.aspx.cs.50", thisCustomer.SkinID, thisCustomer.LocaleSetting)));

            // NOTE:
            //  Because of the Cache API uses unique keys cache entries
            //  We SHOULD LEAVE OUT the CurrentDate parameters since DateTime.Now
            //  is NON-Deterministic, it will return a new unique value EVERYTIME it's called
            //  Hence adding it to our cache key would work out against us since we'll be adding
            //  a NEW cache entry EVERYTIME.
            string query = string.Format("exec GetEcommerceSubstituteItems @ItemCode = {0}, @WebSiteCode = {1}, @ContactCode = {2}, @CurrentDate = {3}, @ProductFilterID = {4}",
                            DB.SQuote(itemCode),
                            DB.SQuote(ConfigInstance.WebSiteCode),
                            DB.SQuote(thisCustomer.ContactCode),
                            DB.SQuote(Localization.DateTimeStringForDB(DateTime.Now)), DB.SQuote(thisCustomer.ProductFilterID));

            var ds = DB.GetDS(query, false);
            if (ds.Tables[0].Rows.Count == 0)
            {
                ds.Dispose();
                return tmpS.ToString();
            }

            //SUBSTITUTE_ITEMS
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                var substituteItems = new XElement("SUBSTITUTE_ITEMS");
                int counter = DB.RowFieldInt(row, "Counter");

                string displayName = DB.RowField(row, "ItemDescription");
                if (displayName.IsNullOrEmptyTrimmed())
                {
                    displayName = DB.RowField(row, "ItemName");
                }

                substituteItems.Add(new XElement("SHOWPICS", showPics.ToString().ToLowerInvariant()));
                substituteItems.Add(new XElement("COUNTER", counter));
                substituteItems.Add(new XElement("ITEM_NAME", displayName));
                substituteItems.Add(new XElement("SHOW_EXTENDED_TEXT", AppLogic.GetString("common.cs.16", thisCustomer.SkinID, thisCustomer.LocaleSetting)));

                string itemLink = SE.MakeProductLink(counter.ToString(), displayName);
                if (showPics)
                {
                    string subItemCode = InterpriseHelper.GetInventoryItemCode(counter);
                    ImgFilename = ProductImage.GetImageFileNameFromInventory("AND IsDefaultIcon = 1", subItemCode, string.Empty);

                    string imageUrl = AppLogic.LocateImageFilenameUrl("Product", subItemCode, "icon", ImgFilename, AppLogic.AppConfigBool("Watermark.Enabled"), out exists);
                    string seTitle = string.Empty;
                    string seAltText = string.Empty;
                    AppLogic.GetSEImageAttributes(subItemCode, "icon", thisCustomer.LanguageCode, ref seTitle, ref seAltText);

                    var imageXml = new XElement("IMAGE");
                    imageXml.Add(new XElement("IMG_URL", imageUrl));
                    imageXml.Add(new XElement("IMG_ALT_TEXT", seAltText));
                    imageXml.Add(new XElement("IMG_TITLE_TEXT", seTitle));
                    imageXml.Add(new XElement("ITEM_LINK", itemLink));
                    substituteItems.Add(imageXml);
                }

                substituteItems.Add(new XElement("ITEM_LINK", itemLink));
                string webDescription = DB.RowField(row, "WebDescription");

                if (!webDescription.IsNullOrEmptyTrimmed() && AppLogic.ReplaceImageURLFromAssetMgr)
                {
                    webDescription = webDescription.Replace("../images", "images");
                    substituteItems.Add(new XElement("WEB_DESCRIPTION", webDescription));
                }
                else
                {
                    substituteItems.Add(new XElement("EXTENDED_DESCRIPTION", DB.RowField(row, "ExtendedDescription")));
                }

                xml.Add(substituteItems);
            }

            return new XmlPackage2(helperTemplate, xml).TransformString();
        }

       public static string GetPostalCityStates(string country, string postalCode, string stateCode)
       {

           string _output = AppLogic.UNDEFINED_RESULT;
           SqlConnection con = DB.NewSqlConnection();

           try
           {
               con.Open();
               string sqlQuery = string.Empty;
               

               // --> short circuit if state code is not empty

               if (!string.IsNullOrEmpty(stateCode))
               {
                   int count = 0;

                   sqlQuery = string.Format("SELECT COUNT(StateCode) As IsPostalGood FROM SystemPostalCode with (NOLOCK) WHERE PostalCode = '{0}' AND CountryCode = '{1}' AND StateCode='{2}'", postalCode, country, stateCode);
                   IDataReader _reader = DB.GetRSFormat(con, sqlQuery);

                   if (_reader.Read())
                   {
                       count = DB.RSFieldInt(_reader, "IsPostalGood");

                   }

                   return count.ToString();

               }
               
               // <--

               string customerCode = Customer.Current.CustomerCode;

               sqlQuery = string.Format("EXEC EcommerceFindCityStates '{0}', '{1}', 1", country, postalCode);

               IDataReader reader = DB.GetRSFormat(con, sqlQuery);
               StringBuilder listing = new StringBuilder();
              
               int counter = 0;
               string citystates = string.Empty;
               string city = string.Empty;
               string state = string.Empty;

               CountryAddressDTO _country = CountryAddressDTO.Find(country);
               bool withStates = _country.withState;

               while (reader.Read())
               {

                  state = DB.RSField(reader, "StateCode");
                  city =  DB.RSField(reader, "City");

                  if (!withStates)
                  {
                      citystates = string.Format("{0}", city);

                  }else{

                      citystates = string.Format("{0}, {1}", state, city);
                  }

                  listing.AppendFormat("<option value='{0}'>{0}</option>", citystates);

               }


               if (counter == 0)
               {


               }

               _output = listing.ToString();

           }
           catch (Exception ex)
           {
               _output = string.Format("Runtime Error @ Interprise.GetPostalCityStates: {0}", ex.Message);
           }
           finally
           {
               con.Close();
               con.Dispose();
           }


           return _output;

       }

       public static string[] GetStoreEmailAccountInfo()
       {
           string[] emailacctinfo = new string[] { };
           string emailadd = string.Empty;
           string name = string.Empty;
           string usercode = string.Empty;

           emailacctinfo = Interprise.Facade.Base.SimpleFacade.Instance.GetRow(
               new string[] { Interprise.Framework.Base.Shared.Const.CONNECTEMAILACCOUNT_EMAILACCOUNTCODE_COLUMN, Interprise.Framework.Base.Shared.Const.CONNECTEMAILACCOUNT_NAME_COLUMN, Interprise.Framework.Base.Shared.Const.CONNECTEMAILACCOUNTVIEW_USERCODE_COLUMN },
               "CONNECTEMAILACCOUNTVIEW",
               String.Format("{0} = (SELECT {0} FROM {1} WHERE {2}='{3}')",
               Interprise.Framework.Base.Shared.Const.CONNECTEMAILACCOUNT_EMAILACCOUNTCODE_COLUMN, Interprise.Framework.Base.Shared.Const.ECOMMERCESITE_TABLE,
                Interprise.Framework.Base.Shared.Const.ECOMMERCESITE_WEBSITECODE_COLUMN, InterpriseHelper.ConfigInstance.WebSiteCode)
                );

           if (emailacctinfo != null)
           {
               if (emailacctinfo.Length > 0)
               {
                   if (!string.IsNullOrEmpty(emailacctinfo[0]))
                   {
                       emailadd = emailacctinfo[0];
                   }

                   if (emailacctinfo.Length >= 2)
                   {
                       if (!string.IsNullOrEmpty(emailacctinfo[1]))
                       {
                           name = emailacctinfo[1];
                       }
                   }

                   if (emailacctinfo.Length >= 3)
                   {
                       if (!string.IsNullOrEmpty(emailacctinfo[2]))
                       {
                           usercode = emailacctinfo[2];
                       }
                   }
               }
           }

           return new string[] { emailadd, name, usercode };
       }

       #region GetInventoryFreeStock

       public static decimal GetInventoryFreeStock(string itemCode, string unitMeasureCode, Customer thisCustomer)
       {
           decimal freeStock = decimal.Zero;
           bool isCBN = false;
           int cbnItemID = 0;
           string cbnUMCode = string.Empty;

           if (unitMeasureCode.IsNullOrEmptyTrimmed())
           {
               var umInfo = InterpriseHelper.GetItemDefaultUnitMeasure(itemCode);
               unitMeasureCode = umInfo.Code;
           }

           using (var con = DB.NewSqlConnection())
           {
               con.Open();
               using (var reader = DB.GetRSFormat(con, "SELECT II.IsCBN, II.CBNItemID, SysUM.CBNUnitMeasureCode FROM InventoryItem II  with (NOLOCK) INNER JOIN InventoryUnitMeasure IUM  with (NOLOCK) ON II.ItemCode = IUM.ItemCode " +
                                   "INNER JOIN SystemUnitMeasure SysUM  with (NOLOCK) ON IUM.UnitMeasureCode = SysUM.UnitMeasureCode WHERE II.ItemCode = {0} AND IUM.UnitMeasureCode = {1}", 
                                   itemCode.ToDbQuote(), unitMeasureCode.ToDbQuote()))
               {
                   while (reader.Read())
                   {
                       isCBN = DB.RSFieldBool(reader, "IsCBN");
                       cbnItemID = DB.RSFieldInt(reader, "CBNItemID");
                       cbnUMCode = DB.RSField(reader, "CBNUnitMeasureCode");
                   }
               }
           }

           if (isCBN && !cbnItemID.IsNullOrEmptyTrimmed())
           {
               Interprise.Facade.Base.CBN.CBNTransactionFacade cbnTransactionFacade = new Interprise.Facade.Base.CBN.CBNTransactionFacade();
               freeStock = Convert.ToDecimal(cbnTransactionFacade.CheckSupplierAvailableStock(cbnItemID.ToString(), cbnUMCode));
               freeStock = (freeStock < 0 ? decimal.Zero : freeStock);
           }
           else
           {
               freeStock =  DB.GetSqlNDecimal(string.Format("SELECT SUM(FreeStock) AS N FROM AvailabilityView with (NOLOCK) WHERE ItemCode = {0} AND WarehouseCode = {1} AND UM = {2}",
                      DB.SQuote(itemCode), AppLogic.AppConfigBool("ShowInventoryFromAllWarehouses") ? "WarehouseCode" : DB.SQuote(thisCustomer.WarehouseCode), unitMeasureCode.ToDbQuote()));
           }

           return freeStock;
       }

       #endregion

       #region GetCBNUnitMeasureCode

       public static string GetCBNUnitMeasureCode(string itemCode, string unitMeasureCode)
       {
           return DB.GetSqlS(String.Format("SELECT SysUM.CBNUnitMeasureCode AS S FROM InventoryItem II  with (NOLOCK) " +
                                           "INNER JOIN InventoryUnitMeasure IUM  with (NOLOCK) ON II.ItemCode = IUM.ItemCode " +
                                           "INNER JOIN SystemUnitMeasure SysUM  with (NOLOCK) ON IUM.UnitMeasureCode = SysUM.UnitMeasureCode " +
                                           "WHERE II.ItemCode = {0} AND IUM.UnitMeasureCode = {1}", 
                                           itemCode.ToDbQuote(), unitMeasureCode.ToDbQuote()));
       }

       #endregion 

       public static int GetPostalCodeDigits(string postalCode, bool plus4)
       {
           try
           {
               int index = 0;

               var code = postalCode.Split(new Char[] { '-' });
               string digits = String.Empty;

               if (code.Length > 1)
               {
                   if (plus4) index = 1;

                   digits = code[index].Trim();
                   return CommonLogic.IIF(CommonLogic.IsNumber(code[index]), int.Parse(code[index]), 0);
               }

           } catch {  return 0;  }

           return 0;
       }

    }

    public class MarkedAsObsoleteException : ApplicationException
    {
        private static string _msg = "This part of the program is marked as obsolete, please check if this is still being used";

        public MarkedAsObsoleteException() : base(_msg) { }
        public MarkedAsObsoleteException(string message) : base(message) { }
    }

    public class ToBeImplementedException : ApplicationException
    {
        private static string _msg = "This part is yet to be implemented in the integration, either this must break because of deprecated functions or must be updated!";

        public ToBeImplementedException() : base(_msg) { }
    }
}